Index / HLookup for values ABOVE the row you search in 1

[MattGreer]

I have three rows of data. The term to search for is in the last row, and I want to return the value in the first rwo. Can this be done?

[tt]
123 234 345 456
1.2 2.3 3.4 4.5
Text1 Text2 Text3 Text4[/tt]

So if I look up "Text4", I would return a value of "456".

I appreciate the help!


Thanks!!


Matt

 

[SkipVought]

hi,

You have a classic example why VLOOKUP or HLOOKUP in your specific case cannot work.

VLOOKUP can ONLY work when the lookup values are in the FIRST column.

HLOOKUP can ONLY work when the lookup values are in the FIRST row.

Consequently, I routinely use INDEX and MATCH rather than either of the former functions.
[tt]
=INDEX(1:1,1, MATCH("Text4",3:3,0))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

It should really be
[tt]
=INDEX(1:[highlight]2[/highlight],1, MATCH("Text4",3:3,0))
[/tt]
this way you can return from row [highlight]2[/highlight]
[tt]
=INDEX(1:2,[highlight]2[/highlight], MATCH("Text4",3:3,0))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

Ahh, thanks Skip. I actually just used the "offset" function. I thought it was only for VBA, but it works directly in the spreadsheet too so that's good. I tried index but failed, but will see if I can get it to work as well, just for fun. Star for you, buddy.

Thanks!!


Matt

 

[SkipVought]

The VBA Offset and Spreadsheet Offset are 2 entirely different animals.

I used a very broad brush.

If your table is isolated in the ROWS and COLUMN on the sheet then the table rangecould be GENERALLY defined as
[tt]
=OFFSET(Sheet1!$A$1,0,0, COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
[/tt]
If that were defined in Formulas > Define Names > Name Manager as MyTable then assuming that the LookupRange is ALWAYS the last row in the table, then LookupRange is
[tt]
=OFFSET(MyTable,ROWS(MyTable-1)-1,0,1,COLUMNS(MyTable))
[/tt]

[tt]
=INDEX(MyTable,1, MATCH("Text4",LookupRange,0))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MattGreer]

I see the benefit if what you are saying, thank you for the follow up.

For whatever reason, my formula ended up being a bit simpler than what you had, I should investigate further.

=OFFSET($B$24,-2,MATCH("Text4",$B$24:$R$24,0)-1)

Thanks again Skip.

Thanks!!


Matt

 

[SkipVought]

There is more than one way to skin a cat. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}