I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the first
paramater.

So a second paramater is given and so from the first match, I want a second
match made.

Is this possible?

thanks much in advance.

You can only get one return using a VLOOKUP. But, you can get tricky and
use CONCATENATION to create a new lookup column and then look up
CONCATENATED combinations...........
something like...........

=VLOOKUP(A1&B1,D:E,2,FALSE)

where column D has the CONCATENATED value combinations you're looking
for............

Vaya con Dios,
Chuck, CABGx3



"puppy" wrote in message
...
I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the
first
paramater.

So a second paramater is given and so from the first match, I want a
second
match made.

Is this possible?

thanks much in advance.

I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

puppy wrote:

I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the first
paramater.

So a second paramater is given and so from the first match, I want a second
match made.

Is this possible?

thanks much in advance.

--

Dave Peterson

Sounds like you're looking for the Sumproduct() function.
*Not* how it was designed by MS (see Help Files), but as it's being used
today.

Check out this link:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"puppy" wrote in message
...
I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the
first
paramater.

So a second paramater is given and so from the first match, I want a
second
match made.

Is this possible?

thanks much in advance.

OR, you could combine elements of both Chuck's and Dave's examples and does
not need a concatenated helper column.

=INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0))

Entered as an array with the key combo of CTRL,SHIFT,ENTER.

Biff

"puppy" wrote in message
...
I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the
first
paramater.

So a second paramater is given and so from the first match, I want a
second
match made.

Is this possible?

thanks much in advance.

I should have added this:

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)



Dave Peterson wrote:

I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))

puppy wrote:

I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the first
paramater.

So a second paramater is given and so from the first match, I want a second
match made.

Is this possible?

thanks much in advance.

--

Dave Peterson

--

Dave Peterson

But if your data looks like:

aaa bbbb
aaab bbb
a aabbbb

It could yield the incorrect results.

=INDEX(range,MATCH(A1&char(10)&B1,AA1:AA10&char(10 )&AB1:AB10,0))

(using alt-enter as a separator--if it's not used within the strings.)

Biff wrote:

OR, you could combine elements of both Chuck's and Dave's examples and does
not need a concatenated helper column.

=INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0))

Entered as an array with the key combo of CTRL,SHIFT,ENTER.

Biff

"puppy" wrote in message
...
I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the
first
paramater.

So a second paramater is given and so from the first match, I want a
second
match made.

Is this possible?

thanks much in advance.



--

Dave Peterson

Good point!

Biff

"Dave Peterson" wrote in message
...
But if your data looks like:

aaa bbbb
aaab bbb
a aabbbb

It could yield the incorrect results.

=INDEX(range,MATCH(A1&char(10)&B1,AA1:AA10&char(10 )&AB1:AB10,0))

(using alt-enter as a separator--if it's not used within the strings.)

Biff wrote:

OR, you could combine elements of both Chuck's and Dave's examples and
does
not need a concatenated helper column.

=INDEX(range,MATCH(A1&B1,AA1:AA10&AB1:AB10,0))

Entered as an array with the key combo of CTRL,SHIFT,ENTER.

Biff

"puppy" wrote in message
...
I know how to use vlookup() but I have found a new problem.

Say I have a BIG sheet and want to search it for MORE THAN ONE match in
multiple columns.

Further explination:

I do a vlookup() of the table, but there are multiple matches with the
first
paramater.

So a second paramater is given and so from the first match, I want a
second
match made.

Is this possible?

thanks much in advance.



--

Dave Peterson