0

I have a set of columns (H-AZ), each with a name at the top, and below it a series of values. I have a cell (B2) that contains a name. I want to look up the column corresponding to this name (with the name in the first row of that column) and then return the last value from that column. The values are all numbers, but they are not sorted and cannot be.

I can use this formula to get the column number:

=MATCH(B2,$H$1:$AZ$1,0)

But I'm not sure how to look up the last value in a column when all I know is the column number without some really complicated INDIRECT in which I have to re-run the MATCH several times.

I could use an HLOOKUP, but I'm not sure how to specify the row:

=HLOOKUP(B2,$H$1:$AZ,???, FALSE)

I have seen this question but it seems like even in the simplest answer I can find, I would need to re-run the MATCH and then generate an INDIRECT from it a minimum of 4 times.

I am thinking there is probably a simple way to do this that I am just not seeing. I have seen this question which I think is the same as mine except that it is for Excel and mine is for Google Sheets. I have tried adapting it to my use case but have failed. for instance, when I try to use:

=HLOOKUP(B2,$H$1:$AZ,MATCH(9^99,INDEX($H$1:$AZ$1,0,MATCH(E37,INDEX($H$1:$AZ$1,1,0),0))),0)

I get the error:

Did not find value '2.95127E+94' in MATCH evaluation.
Michael
  • 9,060
  • 14
  • 61
  • 123

1 Answers1

0

Nothing like spending hours in a problem only to figure it out two minutes after posting it to SO.

In the last formula, the ranges needs to encompass the entire column:

=HLOOKUP(B2,$H$1:$AZ,MATCH(9^99,INDEX($H$1:$AZ,0,MATCH(E37,INDEX($H$1:$AZ,1,0),0))),0)

Michael
  • 9,060
  • 14
  • 61
  • 123