Kind of a reverse vlookup

[Guest]

In column A there are 9 numbers. They are not consecutive. In Column B there
are 5,000 numbers and the nine numbers from Column A will occur often thruout
the list.

In Column C there are 5,000 numbers that give me info about the B column
value of the same line.

How do I extract the C column value to column D of the same line?

Because those Column C values will all be different for each of the nine
numbers of Column A , a vlookup table can’t be used.

Because they are not consecutive, sort won’t help me.

I don’t want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Col’s B and C.

I’ve been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and I’m dyin to know what you guru’s suggest…

MUCH Thanks,

sam

 

[Guest]

In column D1 and copy down:

=IF(ISNA(MATCH(B1,$A$1:$A$9,0)),"",C1)

If column B matches list in A, C value is placed in D

Extended range A as required (dynamic list if it varies greatly)

Is this what you want?

 

[Guest]

Ahhh, my bad. I wasn't set up to test this. Give me 15 minutes and I'll be
back...

I really appreciate your response. It's gonna be headache if this can't be
figured out.

Sam

 

[Guest]

You the MAN, Toppers.

I never would have figured this out. You completely threw me with ISNA and
MATCH. I never could get MATCH to work because I couldn't figure out how the
"match type" of that command came into play. Still quite don't understand.
I'm gonna play with this awhile.

I am most grateful for your assistance...

HAND,

Sam

 

[Guest]

=IF(ISNA(MATCH(B1,$A$1:$A$9,0)),"",C1)


ISNA handles the "error" condition where there is no match and result i.e
column D, is set to blank. If there is no error i.e. match found, then cell
is set to value in column C.

MATCH compares the value in column B against your list in column A: the zero
parameter in the formula means find an EXACT match.

HTH