Matching one sheet to another

fayeglock

New Member
Joined
Aug 10, 2006
Messages
7
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Joined
Jul 30, 2006
Messages
3,656
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,621
Members
410,804
Latest member
bluepinky
Top