Lookup formula returning same value for multiple lookup values

[wellsrp]

I am using a formula to retrieve information from one worksheet to
another.

To simplify my issue, see if you can understand the example below:

1. Open a new worksheet
2. In Column A, enter the numbers 1 thru 9 down the column
3. In Column B, enter the numbers A thru I down the column
4. In Column F, list the following numbers down the column: 5, 9, 4,
12, 6, 7, 22, 3, 450
5. In Column G, enter the formula: =LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)
6. Copy the formula down and look at the results.

For the values 12, 22 and 450, the logic should have failed, but
returns the bottom-most value from the return value array.

Basically, I am trying to compare two lists, and when an item does not
appear in the first list, it should return a blank value. If the item
does appear, then it should return the corresponding value for a cell
on the sheet.

Any ideas?

Thanks for you help!

Take care.

RPW

 

[swatsp0p]

Instead of using LOOKUP, use VLOOKUP and check for FALSE. If FALSE
return a blank (""), as such:

=IF(ISNA(VLOOKUP(F1,$A$1:$B$9,2,0)),"",VLOOKUP(F1,$A$1:$B$9,2,0))

note the "0" in the formula is the same as FALSE, meaning if an exac
match isn't found, #N/A will be returned. The IF checks for this erro
and returns a blank if the error is found. If no error, then return th
value in the second column (col B).

HT

 

[PCLIVE]

Here's one way:

=IF(COUNTIF($A$1:$A$9,F1)=0,"",(LOOKUP(F1,$A$1:$A$9,$B$1:$B$9)))

Best Regards,
Paul

 

[L. Howard Kittle]

Hi RPW,

Maybe this will do what you want.

=IF(ISNA(VLOOKUP(F1,$A$1:$B$9,2,0)),0,VLOOKUP(F1,$A$1:$B$9,2,0))

HTH
Regards,
Howard