MATCH to return row number not working in HLOOKUP
 

=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

MATCH to return row number not working in HLOOKUP
 

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

HTH,
Bernie
MS Excel MVP


"PBcorn" wrote in message
...
=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

MATCH to return row number not working in HLOOKUP
 

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

"Bernie Deitrick" wrote:

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

HTH,
Bernie
MS Excel MVP


"PBcorn" wrote in message
...
=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