Lookup formula returning same value for multiple lookup values

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

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

"wellsrp" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> wellsrp
> ------------------------------------------------------------------------
> wellsrp's Profile:
> http://www.excelforum.com/member.php...o&userid=27974
> View this thread: http://www.excelforum.com/showthread...hreadid=474764
>

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

"wellsrp" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> wellsrp
> ------------------------------------------------------------------------
> wellsrp's Profile:
> http://www.excelforum.com/member.php...o&userid=27974
> View this thread: http://www.excelforum.com/showthread...hreadid=474764
>