Help getting first column data based on criteria?
You're very welcome....Thanks for the feedback. I'm glad that worked for you.
***********
Regards,
Ron
XL2002, WinXP
"Tami" wrote:
Brillian! Ron- this works perfectly! Thank you so much! I really
appreciate it!
"Ron Coderre" wrote:
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)*RO W(A1:A4)),1),1)
Does that help?
***********
Regards,
Ron
XL2002, WinXP
"Tami" wrote:
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!
"Ron Coderre" wrote:
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:A 4)),0)
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
"Tami" wrote:
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!
|