VlookUp error

[Clive_S]

Hi

Pete Kindly gave me a formulae to check 2 seperate columns for a
value:

=IF(ISNA(MATCH(A2&"",Gazatteer!A$2:B$65001,0)),IF(ISNA(MATCH
(A2&"",Gazatteer!$G3:H$65001, 0)),"not found",VLOOKUP(A2&"",Gazatteer!
$G2:H$65001,1,0)),VLOOKUP(A2&"", Gazatteer!A$2$65001,1,0))

I get not found.

When I use 2 seperate Vlookup, the one works (the data is in either
A2:C65000 or G2:F65000)

The following works:
=VLOOKUP( A2, Gazatteer!A$2:C$65536, 1, 0 ) (A)
=VLOOKUP( A2, Gazatteer!F$2:G$65536, 1, 0 ) (B)

This requires 2 coloumns, rather than a single column??

PS I have tried A2 "",.... & A2,.....

 

[Don Guillett]

Modify this to suit. It looks for a match in the first and if found uses
that. If not,uses the second range. I strongly suggest NOT using 65501
unless your data goes that far

=VLOOKUP(A2,IF(ISNA(MATCH(A2,B:B,0)),F1:G21,B1:C21),2,0)

 

[Clive_S]

Hi Don

the formulae:

=VLOOKUP(A2,IF(ISNA(MATCH(A2,B:B,0)),F1:G21,B1:C21),2,0)

I do not get the MATCH(A2,B:B,0)

What is the B:B (??)

 

[Clive_S]

Hi Don

I am comparing a cell in worksheetA with worksheetB or worksheetC

=VLOOKUP(R3,IF(ISNA(MATCH( R3, Gaz!$A$2:$K$65500,0 )), Gaz!$A$2:$E
$65536, Gaz!$F$2:$K$65500 ),1, 0)

This matchs all the records in GazA2:B65000.

It does not match the cell with records in GazF2:J65000

I have to use 2 sets of columns or 2 seperate worksheets as I have
more than 100,000 records & excel 2003 has 65,000 limit

Thanks

Clive

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

try this ( I think you have your ranges wrong because you want to use the
2nd range IF there is not a match for the 1st, but since calculation should
be slow, I recommend using a FIND macro instead.
=VLOOKUP(R3,IF(ISNA(MATCH( R3, Gaz!$A:$a,0 )), Gaz!$A$2:$E
$65536, Gaz!$F$2:$K$65536 ),1, 0)

 

[Clive_S]

Hi Don=VLOOKUP(R3,IF(ISNA(MATCH( R3, Gaz!$A:$J$65536, 0 )), Gaz!$A$2:$E
$65536,
Gaz!$F$2:$J$65536 ),1, 0)

This only returns the records from Gaz!$A$2:$E$65536

Any records in Gaz!$F$2:$K$65536 are written as #N/A


The formula below should work?? Don't understand why it is not??

=IF(ISNA(MATCH(R2&"",Gaz!A$2:E$65000,0)),IF(ISNA(MATCH(R2&"",Gaz!$F$2:J
$65000,0)),"not found",VLOOKUP(R2&"",Gaz!$F$2:J$65000, 1,0)),VLOOKUP
(R2&"",Gaz!A$2:E$65000, 1,0))

 

[Clive_S]

Hi Don,

If I understand you, I will need to have to match twice??

match GazA2:E65000
=VLOOKUP(R2,IF(ISNA(MATCH( R2, Gaz!$A$2:$J$65500,0 )), Gaz!$A$2:$E
$65500, Gaz!$F$2:$J$35500 ), 1, 0)

Then match GazF2:J65000
=VLOOKUP(R2,IF(ISNA(MATCH( R2, Gaz!$A$2:$J$65500,0 )), Gaz!$F$2:$J
$35500 Gaz! $A$2:$E$65500 ), 1, 0)


I still don't understand why Peter's formula doesn't work. Matching
all

=IF(ISNA(MATCH(R2&"",Gaz!B$2:B$65000,0)),IF(ISNA(MATCH(R2&"",Gaz!$F$2:J
$65000,0)),"not found",VLOOKUP(R2&"",Gaz!$F$2:J$65000,2,0)),VLOOKUP
(H2&"",Gaz!A$2:E$65000,2,0))

 

[Don Guillett]

The formula I gave you was fully tested and worked as written. Did you
change the order of the ranges as suggested.
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.