Matching one sheet to another

[fayeglock]

Looking at col A in Sheet 1, I want to look to see if col A in Sheet 2 has a match. If so, I want to pull the value from col B in Sheet 2 and put it in col B in Sheet A. When there is no match, lookup is putting the closest value in. I want the unmatched rows to be blank or zero.

I've tried 'lookup' and 'vlookup' and 'If', but can't seem to get what I need.

Thanks for any tips you may have.
Faye

 

[PaddyD]

hi.

1) set the 4th argument in the vlookup() to false to force an exact match
2) embed the vlookup() in another formula to take care of the fact that vlookup() returns na when doing an exact match lookup & the item can't be found

see:

http://www.mrexcel.com/board2/viewtopic.php?t=62102

for various approaches to (2)

 

[stanleydgromjr]

Try this:

You may want to change the range: Sheet2!A1:B18

=IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B18,2,FALSE)),"",VLOOKUP(A1,Sheet2!A1:B18,2,FALSE))


Have a great day,
Stan

 

[PaddyD]

Stan,

A word of caution with the iserror() approach - it masks all errors, including syntactic ones in the formula construction:

=IF(ISERROR(VLKUP(A1,Sheet2!A1:B18,2,FALSE)),"",VLOOKUP(A1,Sheet2!A1:B18,2,FALSE))

...is not an error you'd want to mask. Better to check either specifically for the error you're monitoring for (using isna() in this case), or, more generally, for the condition that generates the error in the first place.