MATCH to return row number not working in HLOOKUP

[PBcorn]

=HLOOKUP($E$1,'G:\dir1\dir2\dir3\[Book1.xls]Sheet1'!$B$16:$AB$25,MATCH($B20,'G:\dir1\dir2\dir3\[Book1.xls]Sheet1'!$B$17:$B$25,0)+1,FALSE)

TABLE 1:

COL B

1.2
2.2
3.2

TABLE 2:

COL B

1.2
2.2
3.2

trying to use MATCH to return the row for a HLOOKUP formula but it's not
working (just gives a N/A but workes fine when outside the hlookup formula) -
any suggestions??

Thanks

 

[Bernie Deitrick]

Move the MATCH to its own cell, then reference that cell in your HLOOKUP formula.

HTH,
Bernie
MS Excel MVP

 

[PBcorn]

Thanks, It seems to be working now, just closed both books and opened them in
same instance of excel.

Move the MATCH to its own cell, then reference that cell in your HLOOKUP formula.

HTH,
Bernie
MS Excel MVP

=HLOOKUP($E$1,'G:\dir1\dir2\dir3\[Book1.xls]Sheet1'!$B$16:$AB$25,MATCH($B20,'G:\dir1\dir2\dir3\[Book1.xls]Sheet1'!$B$17:$B$25,0)+1,FALSE)

TABLE 1:

COL B

1.2
2.2
3.2

TABLE 2:

COL B

1.2
2.2
3.2

trying to use MATCH to return the row for a HLOOKUP formula but it's not
working (just gives a N/A but workes fine when outside the hlookup formula) -
any suggestions??

Thanks