Kind of a reverse vlookup
 

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 cant be used.

Because they are not consecutive, sort wont help me.

I dont want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Cols B and C.

Ive been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and Im dyin to know what you gurus suggest€¦

MUCH Thanks,

sam

Kind of a reverse vlookup
 

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?

"sam" wrote:

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 cant be used.

Because they are not consecutive, sort wont help me.

I dont want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Cols B and C.

Ive been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and Im dyin to know what you gurus suggest€¦

MUCH Thanks,

sam

Kind of a reverse vlookup
 

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

"Toppers" wrote:

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?

"sam" wrote:

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 cant be used.

Because they are not consecutive, sort wont help me.

I dont want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Cols B and C.

Ive been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and Im dyin to know what you gurus suggest€¦

MUCH Thanks,

sam

Kind of a reverse vlookup
 

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

"sam" wrote:

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

"Toppers" wrote:

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?

"sam" wrote:

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 cant be used.

Because they are not consecutive, sort wont help me.

I dont want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Cols B and C.

Ive been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and Im dyin to know what you gurus suggest€¦

MUCH Thanks,

sam

Kind of a reverse vlookup
 

=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

"sam" wrote:

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

"sam" wrote:

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

"Toppers" wrote:

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?

"sam" wrote:

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 cant be used.

Because they are not consecutive, sort wont help me.

I dont want to do something manually, because those 9 numbers of column A
could easily be 800 numbers, with 50,000 numbers in Cols B and C.

Ive been using an IF(OR formula, but that only allows 30 arguments.

I am stumped, and Im dyin to know what you gurus suggest€¦

MUCH Thanks,

sam