Need to do an EXACT LOOKUP

[Guest]

I want to compare Column A from two worksheets, and copy Column B from the
second into Column B if they are exact. For cell A1, if I use
=LOOKUP(A1,'Report'!A$2:A$6500,'Report!B$2:B$6500), I end up getting the
'closest fit', but I would rather have blanks if they do not exactly match.
If I use something like
=IF(EXACT(A1,'Report'!A$2:A$6500),'Report'!B$2:B$6500,""), then the formula
gives me the last value in the array comparison, which is always blank.

I can probably combine the LOOKUP with an AND IF(EXACT) somehow that will
weed out the ones I don't want, but there has to be a simple formula or
function for this.

 

[Niek Otten]

Don't use LOOKUP, use VLOOKUP.
If you require an exact match, the 4th argument should be set to FALSE
Look in HELP for details

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to compare Column A from two worksheets, and copy Column B from the
| second into Column B if they are exact. For cell A1, if I use
| =LOOKUP(A1,'Report'!A$2:A$6500,'Report!B$2:B$6500), I end up getting the
| 'closest fit', but I would rather have blanks if they do not exactly match.
| If I use something like
| =IF(EXACT(A1,'Report'!A$2:A$6500),'Report'!B$2:B$6500,""), then the formula
| gives me the last value in the array comparison, which is always blank.
|
| I can probably combine the LOOKUP with an AND IF(EXACT) somehow that will
| weed out the ones I don't want, but there has to be a simple formula or
| function for this.