Help getting first column data based on criteria?

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Brillian! Ron- this works perfectly! Thank you so much! I really
appreciate it!

 

[Guest]

You're very welcome....Thanks for the feedback. I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP