Multiple VLOOKUP

[Dave]

Scanned through a bunch of past vlookup questions but did
not find something similar:

On Sheet "Quotes" I enter a customer name and phone
number amongst other stuff.
On Sheet "Leads", the same type of info is entered.

When a user enters new data on "Quotes" I currently have
a cell that does a vlookup to check for matching phone
numbers in the "Leads" sheet. AND a separate cell that
does a vlookup to see if the name matches.
Column A: =VLOOKUP(C2,name,2,FALSE)
Column B: =VLOOKUP(C2,phone,2,FALSE)
It just returns a "Match" if there is a match.

I would like to only have one cell that does both. An IF
VLOOKUP I guess. If it finds a matching name, then
return "Name Match" if it finds a matching PH# then
return "Phone Match" Else " ".

(Thousands of entries in each sheet and I do not want to
have the "Leads" sheet sorted.)

Any help is appreciated

Dave

 

[JulieD]

Hi Dave

not sure why you're using VLOOKUP in this situation, i would do the
following
=IF(ISNA(MATCH(D3,A2:A4,0)),IF(ISNA(MATCH(D3,B2:B4,0)),"","phone
matched"),"name matched")
where A2:A4 is the range containing the names and
B2:B4 is the range containing the phone numbers

Cheers
JulieD

 

[Dave]

Julie,

That is awesome!
It not only works great, and easy, but I also learned
about MATCH. Did not even know it existed! Already
envisioning uses for it in leiu of LOOKUPS

Thanks!

Dave

 

[Dave]

Julie,

Can you answer this. It worked great on a quick test
sheet, but when I inserted it into the real worksheet
where the range is an entire column, everytime I enter or
update a value in acell in that range it will "Calculate"
for about 5 seconds. If I make the range smaller, only
5000 rows, then it will calc in about 1 second.

??? Why so long to calc and is there a work around?

Dave

 

[JulieD]

Hi Dave

sorry can't help you on that one ... there's others on the board much more
into performance related issues in excel who may be able to provide a
comprehensive explaination and a faster / better / more roubust formula ..
guys?

Cheers
JulieD