Return Value from Named Range 2

[Scott24x7]

All,
I am having trouble getting a value to return that I need from a table in a named range. I use VLOOKUP and HLOOKUP all the time, but in this case, the value I'm looking for is not the first column, and I need to return the 1st column value when I match the value anywhere in the table.

For example:

John     Blue     Tractor     Case
Bill     Green    Wagon       John Deer
Scott    Purple   Wheels      Good Year     Rubber
Mary     Turtle   Dead

The data above shows that the range can have blank values, which is fine. But what I need to do is say, locate "John Deer", and then return "Bill". VLOOKUP won't let me do that and I've tried lots of things with INDEX and MATCH, but the help is less than clear on their usage, especially in combination. Can someone help me with this? (Assume Name is Name is column A, then B, C, etc, and John is on Row 1).

I have something like:
=INDEX(PersonLookup,MATCH(C21,PersonLookup,0),1)

Where PersonLookup is a named range including all the data above, and C21 = "John Deer" from elsewhere on my spreadsheet.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[yogia]

Hi Scott:

If your data as shown in your post is in cells A1:E4, and G2 houses John Deer then use the following formula ...

=INDEX(A:E,SUMPRODUCT((A1:E4=G2)*ROW(A1:E4)),1)

to get Bill as the result.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[Scott24x7]

Yogi,
Well, it defets the purpose if I specify the cell in the formula... i.e. A1:E4=G2. I have 2 sheets. The data above is defined in the named range "PersonLookup", and what I want to return is the corresponding name from the range that matches on a field that isn't the name. So your suggestion only works for me if I specify the field it matches, and that is not dynamic.


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[Scott24x7]

Yogi,
I played with what you have, this way:

=INDEX(PersonLookup,SUMPRODUCT((PersonLookup=C21)*ROW(PersonLookup)),1)

I did get a name back but it was 4 rows lower than the line that has the C21 value in it...

Don't know if this helps, but I have a "Detail" tab that has detail rows on it. I have a "Data" tab which I use to maintain array elements for several "lookup" functions. Where I can use VLOOKUP they all work great, and its making it a lot easier to maintain data, but, I can't get this function to work, which will be really useful in the long term maintainability of my spreadsheet... so appreciate any help.



Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[combo]

If your data is a bit regular and you know which column to search:
=INDEX(PersonLookup,MATCH(C211,OFFSET(PersonLookup,0,3,ROWS(PersonLookup),1),0),1)

combo

 

[GlennUK]

Does PersonLookup range begin on row 5 by any chance? If so, then try replacing ROW(PersonLookup) with (ROW(PersonLookup)-4)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Scott24x7]

Glenn,
Great eye! That's exactly where the range starts (Row 5). That did the trick Thanks to you both, stars awarded!!!


Best Regards,
Scott

"Everything should be made as simple as possible, and no simpler."

 

[GlennUK]

My pleasure! :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.