#REF with vlookup

[Clive_S]

Hi

I am trying to match 2 addresses and return a text value (looks like a
number).

The list exceeds the Excel max row number, so needs to be split over 2
sets of colums ie B2:B65000 &.H2:H38410

Any help would be appreciated!!

=IF(ISTEXT(VLOOKUP(B2 &"",Sheet2!B$2:B$65001,3,FALSE))= TRUE, "
",VLOOKUP(B2 &"",Sheet2!$H2:H$38410,9,FALSE))

Get #REF

The address is identical in both worksheets???

 

[Daryl S]

In both cases, you are looking up into a single column (either B or H), but
your column references are outside that (3 and 9). You either need to fix
your lookup table (e.g. B$2$65000 if you want to return the value in column
3, and H$2$38410 to to return the value in the 9th column of that table) or
change the column reference to 1.

I also see you have $H2 referenced - you probably mean H$2 in the second
VLOOKUP.