Match value in one column and return value in same row in next col
 

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

Match value in one column and return value in same row in next col
 

"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

Match value in one column and return value in same row in next
 

Thank you so much it works perfect!

"Joe User" wrote:

"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