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 -

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?

CodePudding user response：

INDEX/MATCH Over VLOOKUP or HLOOKUP

• 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.

CodePudding user response：

If you have Excel 365, you can use Xlookup as described in Example 5 of