 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / August 2006Help getting first column data based on criteria?
Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! Perhaps one of these methods: Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP > Hi there- there seem to be quite a few of these posts, but I can't seem to > get it. I have the following: [quoted text clipped - 8 lines] > "pen" and "green" when a table is searched). Any thoughts? Many thanks in > advance! Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I tried your second method. However, it's pulling up the reference number for the row beneath the row that the data is on. Any ideas? Many thanks again! > Perhaps one of these methods: > [quoted text clipped - 27 lines] > > "pen" and "green" when a table is searched). Any thoughts? Many thanks in > > advance! Well.....I guess I could have checked my formula before I posted it :\ Here's an alternative that works (and is easier to decypher): F3: =INDEX(A1:A4,MAX(SUMPRODUCT((B1:B4&C1:C4=F1&F2)*ROW(A1:A4)),1),1) Does that help? *********** Regards, Ron XL2002, WinXP > Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I > tried your second method. However, it's pulling up the reference number for [quoted text clipped - 31 lines] > > > "pen" and "green" when a table is searched). Any thoughts? Many thanks in > > > advance! Brillian! Ron- this works perfectly! Thank you so much! I really appreciate it! > Well.....I guess I could have checked my formula before I posted it :\ > [quoted text clipped - 44 lines] > > > > "pen" and "green" when a table is searched). Any thoughts? Many thanks in > > > > advance! You're very welcome....Thanks for the feedback. I'm glad that worked for you. *********** Regards, Ron XL2002, WinXP > Brillian! Ron- this works perfectly! Thank you so much! I really > appreciate it! [quoted text clipped - 47 lines] > > > > > "pen" and "green" when a table is searched). Any thoughts? Many thanks in > > > > > advance! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.