Help with vlookup

**Ice.Ryuzaki** · 03-01-2009, 03:46 PM

I am having trouble understanding the vlookup function in excel. I need to be able to compare the values of two columns (customer number and salesperson number) in two worksheets. If the two columns in each worksheet do not match I want to return the customer number in a third worksheet. I have tried to follow vlookup tutorials online, but have not been able to make vlookup work the way I need it to. Can anyone help me?

Thanks,

Ice

**JBeaucaire** · 03-01-2009, 03:53 PM

VLOOKUP matches one value to a column of data, then returns a value to the right of the match in another column.

So, if on Sheet2 you had names and IDs like so:

Please Login or Register  to view this content.

Then on Sheet1 you were had a name in cell A2, in B2 you could put this formula to "VLOOKUP" the ID number over on Sheet2:

=VLOOKUP(A1,Sheet2!$A$2:B$20,2,0)

So, if you were comparing A2 to B2 and if they don't match, looking up the customer ID on sheet2 in column B and the value to match to your A1 is in column A on Sheet2, then the formula would look something like this:

=IF(A2=B2,"Match",VLOOKUP(A1,Sheet2!$A$2:B$20,2,0))

**JBeaucaire** · 03-01-2009, 04:21 PM

Cross post I believe:
http://www.mrexcel.com/forum/showthread.php?t=374608

...which is a no-no unless you list the cross-post links. Best though to pick a forum and go with it.

**Ice.Ryuzaki** · 03-01-2009, 04:23 PM

I have tried to make the formula your provided me work, but I am still having trouble. I am trying to use vlookup to search two entire columns on both sheets and return the customer number if the two column key does not match. IS the forumula you posted above able to do that?

For example:
Sheet 1
Customer # Sales Person Code
123 546
456 546

Sheet 2
Customer # Sales Person Code
123 546
456 678

If I can get my vlookup formula to work the way I want it to the formula on sheet three would display "456" as the two columns do not match between the two worksheets.

Thanks!!!

**JBeaucaire** · 03-01-2009, 04:34 PM

This is one way, though I haven't addressed the "blanks" that result, probably could.

Anyway, on sheet3, enter this formula anywhere and press CTRL-SHIFT-ENTER to activate the array in the first cell. Now copy it down and the numbers of concern will appear.

=IF(ISERROR(MATCH(Sheet2!A2&"-"&Sheet2!B2,Sheet1!$A$2:$A$100&"-"&Sheet1!$B$2:$B$100,0)),Sheet2!A2,"-")