#REF error with Vlookup

  • Thread starter Thread starter Clive_S
  • Start date Start date
C

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 (error)

OR

=IF(ISNA(VLOOKUP(A4,Sheet2!B$2:B$65001,3,FALSE))= TRUE, "Not
found",VLOOKUP(A4,Sheet2!$H4:H$38410,9,FALSE))

Get Not found (error)

The address is identical in both worksheets???
 
A few things:

you don't need the =TRUE bit

your column number is 3, but your table is only 1 column wide for
the first VLOOKUP

your column number is 9, but your table is only 1 column wide for
the second VLOOKUP

you can use 0 instead of FALSE

you need to check one table, then the other.

I suggest something like this:

=IF(ISNA(MATCH(B2&"",Sheet2!B$2:B$65001,0)),IF(ISNA(MATCH(B2&"",Sheet2!
$H2:H$38410,0)),"not found",VLOOKUP(B2&"",Sheet2!$H2:J
$38410,3,0)),VLOOKUP(B2&"",Sheet2!B$2:D$65001,3,0))

(all one formula - be wary of spurious line-breaks). If successful,
the VLOOKUPs will return data from the third column (i.e column D or
column J). I think that's what you wanted.

Hope this helps.

Pete
 
Back
Top