#REF error with Vlookup

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???
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

#REF with vlookup 1
VLookup to multiple files 1
VlookUp error 8
Vlookup and Hlookup with an IF condition 0
Vlookup-Doesn't return values under 1 1
VLOOKUP + Something 1
VLookup????? 2
VLOOKUP #REF error 3

Top