Prev: Retain pivot table formatting after refreshing data
Next: Counting Cells by Format
From: Zuo on 6 Mar 2010 14:43
Hi,

I have a a set of values in A1 through A100. I need to look up each value
and find a match in another set of values located in C1 through C200. If a
match is found then I need the formula located in column B to return the
value in the same row but the next column over (D).

Example:

A B C D
359 375 10012
456 125 10031
125 10031 952 10052
854 444 10017


The formula located in B3 should find that the value of 125 (located in A3)
matches the value in C2 and returns 10031 which is the value located in the
next column over in the same row.

Thanks,

Zuo
From: Joe User on 6 Mar 2010 15:13
"Zuo" wrote:
> I have a a set of values in A1 through A100.
> I need to look up each value and find a match in
> another set of values located in C1 through C200.
> If a match is found then I need the formula located
> in column B to return the value in the same row but
> the next column over (D).

=IF(ISNUMBER(VLOOKUP(A1,$C$1:$D$200,2,0)),
VLOOKUP(A1,$C$1:$D$200,2,0), "")

Copy down through A100.

If you have Excel 2007, I believe this can be written more succinctly. I do
not have Excel 2007, but I believe the following syntax works:

=IFERROR(VLOOKUP(A1,$C$1:$D$200,2,0)), "")


----- original message -----

"Zuo" wrote:
> Hi,
>
> I have a a set of values in A1 through A100. I need to look up each value
> and find a match in another set of values located in C1 through C200. If a
> match is found then I need the formula located in column B to return the
> value in the same row but the next column over (D).
>
> Example:
>
> A B C D
> 359 375 10012
> 456 125 10031
> 125 10031 952 10052
> 854 444 10017
>
>
> The formula located in B3 should find that the value of 125 (located in A3)
> matches the value in C2 and returns 10031 which is the value located in the
> next column over in the same row.
>
> Thanks,
>
> Zuo
 | 
Pages: 1
Prev: Retain pivot table formatting after refreshing data
Next: Counting Cells by Format