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

 

[Billy Liddel]

Clive

You basic problem is that the lookup only contains one column when every
column in the table must be included - the offset column will not lookup
anything not included in the table, while the lookup reference is anways the
first column.

something like this should work:
=IF(ISERROR(VLOOKUP(B2,rng1,2,0)),VLOOKUP(B2,rng2,2,0),VLOOKUP(B2,rng1,2,0))

creating range names for the tables means you do not have to to hardcode the
references to sheets or cells.

IFH please tick yes.

Peter Atherton