CODEWITHSUNDEEP
excelexcel-formulavlookup
Mohammed Mustafa
Mohammed Mustafa

Reputation: 13

Vlookup with Hlookup wrong cell value (Excel)

I'm trying to use VLOOKUP with HLOOKUP to extract a value from table but it always give me wrong cell - offset the cell value by one -

1

The equation is

=VLOOKUP(G22,A5:Z18,HLOOKUP(H22,B3:Z4,1,FALSE),FALSE)

the cell in red rec. is the right answer, but it always return value in green circle What is wrong with my code?

Upvotes: 1

Views: 336

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34180

If you have Excel 365, you can use Xlookup as described in Example 5 of This reference. Note that Xlookup can return a whole column from a 2d array:

=XLOOKUP(G22,A5:A18,XLOOKUP(H22,B3:K3,B5:K18))

or if the data is in fact a table where the Phi is the first column and the mm are the headers, this would be:

=XLOOKUP(G22,Table1[φ],XLOOKUP(H22,Table1[#Headers],Table1))

Note If the mm are in the headers, they are formatted as text so H22 has to be formatted to reflect this or use

=XLOOKUP(G22,A5:A18,XLOOKUP(TEXT(H22,"general"),B3:K3,B5:K18))

and

=XLOOKUP(G22,Table1[φ],XLOOKUP(TEXT(H22,"general"),Table1[#Headers],Table1))

enter image description here

Upvotes: 0

VBasic2008
VBasic2008

Reputation: 54777

INDEX/MATCH Over VLOOKUP or HLOOKUP

enter image description here

  • As a rule of thumb, using HLOOKUP only makes sense if you have more rows and you want to return a value from any but the first row. Similarly using VLOOKUP only makes sense if you have more columns and you want to return a value from any but the first column.

  • A more flexible handling of lookups is achieved by using INDEX with MATCH.

    =INDEX(A3:Z18,MATCH(G22,A3:A18,0),MATCH(H22,A3:Z3,0))

  • If someone enters 3 in G22, an error will be displayed. A simple way of error handling is using the IFERROR function.

    =IFERROR(INDEX(A3:Z18,MATCH(G22,A3:A18,0),MATCH(H22,A3:Z3,0)),"")

  • Study the image closely. When it comes to finding exact matches, I never use VLOOKUP or HLOOKUP because INDEX with MATCH covers it all and more. That doesn't mean that you should abandon using them because they are good tools to get familiar with indexes, offsets, and whatnot.

Upvotes: 1

Related Questions