Vlookup not working because of duplicate matches
 

I have tried a little bit of everything (VLOOKUP, LOOKUP, INDEX, SMALL) but
to no avail. Here is my problem:

Worksheet 1:
A B C D E
1 xxxx xxxx 1234 100 xxxx
2 xxxx xxxx 1234 200 xxxx
3 xxxx xxxx 1234 300 xxxx
4 xxxx xxxx 4567 100 xxxx

Worksheet 2:
A B C
1 1234 200

In cell C1 above, I need a formula that looks in Worksheet 1 for the value
in A1 (more than one record) and the value in A2 (the result should be row 2)
and produce the value in the 5th column of that row (cell E2)

I hope this makes sense and I hope someone out there can help me.

Vlookup not working because of duplicate matches
 

Sorry about the typo below:
In cell C1 above, I need a formula that looks in Worksheet 1 for the value
in A1 (more than one record) and the value in B1 (the result should be row 2)
and produce the value in the 5th column of that row (cell E2)

Thanks

"BBS" wrote:

I have tried a little bit of everything (VLOOKUP, LOOKUP, INDEX, SMALL) but
to no avail. Here is my problem:

Worksheet 1:
A B C D E
1 xxxx xxxx 1234 100 xxxx
2 xxxx xxxx 1234 200 xxxx
3 xxxx xxxx 1234 300 xxxx
4 xxxx xxxx 4567 100 xxxx

Worksheet 2:
A B C
1 1234 200

In cell C1 above, I need a formula that looks in Worksheet 1 for the value
in A1 (more than one record) and the value in A2 (the result should be row 2)
and produce the value in the 5th column of that row (cell E2)

I hope this makes sense and I hope someone out there can help me.

Vlookup not working because of duplicate matches
 

This seemed to work for me when I entered it as an array formula
(Cntl-shift-Enter)

=INDEX(Sheet1!$E$1:$E$4,MATCH(A1 & B1,Sheet1!$C$1:$C$4 &
Sheet1!$D$1:$D$4,0),1)
--
Kevin Vaughn


"BBS" wrote:

I have tried a little bit of everything (VLOOKUP, LOOKUP, INDEX, SMALL) but
to no avail. Here is my problem:

Worksheet 1:
A B C D E
1 xxxx xxxx 1234 100 xxxx
2 xxxx xxxx 1234 200 xxxx
3 xxxx xxxx 1234 300 xxxx
4 xxxx xxxx 4567 100 xxxx

Worksheet 2:
A B C
1 1234 200

In cell C1 above, I need a formula that looks in Worksheet 1 for the value
in A1 (more than one record) and the value in A2 (the result should be row 2)
and produce the value in the 5th column of that row (cell E2)

I hope this makes sense and I hope someone out there can help me.

Vlookup not working because of duplicate matches
 

Hi!

Is the data in column E text or numeric?

If it's numeric and you woun't have duplicate lookup values then you can use
Sumproduct:

=SUMPRODUCT(--(Sheet1!C$1:C$4=A1),--(Sheet1!D$1:D$4=B1),Sheet1!E$1:E$4)

Biff

"BBS" wrote in message
...
I have tried a little bit of everything (VLOOKUP, LOOKUP, INDEX, SMALL) but
to no avail. Here is my problem:

Worksheet 1:
A B C D E
1 xxxx xxxx 1234 100 xxxx
2 xxxx xxxx 1234 200 xxxx
3 xxxx xxxx 1234 300 xxxx
4 xxxx xxxx 4567 100 xxxx

Worksheet 2:
A B C
1 1234 200

In cell C1 above, I need a formula that looks in Worksheet 1 for the value
in A1 (more than one record) and the value in A2 (the result should be row
2)
and produce the value in the 5th column of that row (cell E2)

I hope this makes sense and I hope someone out there can help me.

Vlookup not working because of duplicate matches
 

Kevin,

It worked fine in my test spreadsheet, but when I entered the formula in my
actual worksheet, I got #NUM!

Here is my formula in Cell E14 (worksheet 2006 Jan):

=INDEX(Data!T:T,MATCH('2006 Jan'!B14 & '2006 Jan'!D14,Data!Q:Q &
Data!S:S,0),1)

Worksheet (Data):

Q S T
547745 100 -100

Worksheet (2006 Jan):
B D E
14 547745 100 (the result of the formula should be -100)

Vlookup not working because of duplicate matches
 

You can't use entire column references in the Match function because that is
an array formula.

See my reply about using Sumproduct. Note however, that you can't use entire
column references in Sumproduct, either.

Biff

"BBS" wrote in message
...
Kevin,

It worked fine in my test spreadsheet, but when I entered the formula in
my
actual worksheet, I got #NUM!

Here is my formula in Cell E14 (worksheet 2006 Jan):

=INDEX(Data!T:T,MATCH('2006 Jan'!B14 & '2006 Jan'!D14,Data!Q:Q &
Data!S:S,0),1)

Worksheet (Data):

Q S T
547745 100 -100

Worksheet (2006 Jan):
B D E
14 547745 100 (the result of the formula should be -100)

Vlookup not working because of duplicate matches
 

I suspect it is because you are using entire columns. I tried the following
which worked ---
=INDEX(Data!$T$1:$T$3,MATCH(B14 & D14,Data!$Q$1:$Q$3 & Data!$S$1:$S$3,0),1)

but when I changed it to use entire columns, I got the #num error:

=INDEX(Data!$T:$T,MATCH(B14 & D14,Data!$Q:$Q & Data!$S:$S,0),1)
--
Kevin Vaughn


"BBS" wrote:

Kevin,

It worked fine in my test spreadsheet, but when I entered the formula in my
actual worksheet, I got #NUM!

Here is my formula in Cell E14 (worksheet 2006 Jan):

=INDEX(Data!T:T,MATCH('2006 Jan'!B14 & '2006 Jan'!D14,Data!Q:Q &
Data!S:S,0),1)

Worksheet (Data):

Q S T
547745 100 -100

Worksheet (2006 Jan):
B D E
14 547745 100 (the result of the formula should be -100)

Vlookup not working because of duplicate matches
 

Thanks to you both. I tried each of these and they both worked like a
charm!!!!!

Vlookup not working because of duplicate matches
 

You're welcome.
--
Kevin Vaughn


"BBS" wrote:

Thanks to you both. I tried each of these and they both worked like a
charm!!!!!