Using two values in VLOOKUP funcation

[jdunnett]

Hello,

I have a workbook with two worksheets (Sheet1 and Sheet2). Sheet1 has three columns (ID, City, Name). Sheet2 has a (ID, City, ID Code, Name, City).

What I would like to do is use the ID, City from sheet one to look up the ID, City from sheet too to pull in the value of ID Code (should ID, City exist in Sheet2). I think the formula would look something like =IF(D2="",VLOOKUP($A2:$B2,Sheet2!$A$2:$C$2213,3,FALSE)). The thing is it just results in a 0 in the cell with the formula.

The purpose of this is to determine whether or not values in Sheet1 appear in Sheet2.

Can anyone help me out? I hope I have posted this message to the right forum.

Jeff

[teylyn]

Hi, could you possibly post just a small data sample? no need for half a MB to illustrate the issue. Three or four rows should do.

thanks

[jdunnett]

Hi, could you possibly post just a small data sample? no need for half a MB to illustrate the issue. Three or four rows should do.

thanks

I have fixed the above file (with just small data sample). I am sorry for the breach of ettiquette on that one.

Jeff

[davesexcel]

Try,
=IF(COUNTIF(Sheet2!A:A,A2)=0,"Nope","")
drag the formula down
"Nope", will appear if there is no match

[JBeaucaire]

You'll have to clean up your data. There are a lot of straggler "hidden spaces" after some words that will make these matches not work all the time. For instance Sheet2!B166 has the word "Montreal" and two hidden spaces, so it doesn't match the row 26 on Sheet1 like it should.

Anyway, you'll need to clean up your data.

Then put this formula in Sheet1!D2 and copy it down:

=INDEX(Sheet2!$C$1:$C$2500, MATCH(A2&B2, INDEX(Sheet2!$A$1:$A$2500 & Sheet2!$B$1:$B$2500, 0), 0))

=========
Your large file doesn't break any rules or etiquette, it's just overkill. I'd rather see too much data than too little, or more often an example that is too "dumbed down".