VLOOKUP - Multiple Criteria

gregula82

New Member
Joined
Aug 18, 2006
Messages
8
Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.
 
Do you mean something like below?

G2: Sept
G3: Oct
Etc.

H1: 400
I1: 401
Etc.

YES!!!! So here is a small example:

Jan
Feb
Mar
Apl
May
A
10
20
30
40
50
B
20
40
60
80
100
A
30
60
90
120
150
C
40
80
120
160
200

<tbody>
</tbody>


So I need to know what is the total for "A" in May

PLEASE AND THANK YOU!!
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
YES!!!! So here is a small example:

Jan
Feb
Mar
Apl
May
A
10
20
30
40
50
B
20
40
60
80
100
A
30
60
90
120
150
C
40
80
120
160
200

<TBODY>
</TBODY>


So I need to know what is the total for "A" in May

PLEASE AND THANK YOU!!

The data is located in A1:F5.

I2: A

J1: May

J2, just enter:

=SUMIF($A$2:$A$5,$I2,INDEX($B$2:$F$5,0,MATCH(J$1,$B$1:$F$1,0)))
 
Upvote 0
Found the error in my ways. It won't work using a table instead of a normal range of data. You helped alot thank you.
 
Upvote 0
Hi everyone,

I want to make a lookup with three conditions. After reading the first pages of this topic, I managed to get a solution with a formula similar to this: =LOOKUP(2,1/((A1:A100="x")*(B1:B100="y")*(C1:C100="z")),D1:D100).
My problem is that somethimes i got more than one possible answer that match the conditions i stated, and excel only returns one of the results.
Is there any way of making an average of the possible answers that match the criteria?

Sorry if this was already answered in the previous pages.
Thanks.
 
Upvote 0
Hi everyone,

I want to make a lookup with three conditions. After reading the first pages of this topic, I managed to get a solution with a formula similar to this: =LOOKUP(2,1/((A1:A100="x")*(B1:B100="y")*(C1:C100="z")),D1:D100).
My problem is that somethimes i got more than one possible answer that match the conditions i stated, and excel only returns one of the results.
Is there any way of making an average of the possible answers that match the criteria?

Sorry if this was already answered in the previous pages.
Thanks.

F1, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IFERROR(INDEX($D$1:$D$100,SMALL(IF($A$1:$A$100="x",
  IF($B$1:$B$100="y",IF($C$1:$C$100="z",ROW($D$1:$D$100)-ROW($D$1)+1))),
  ROWS($F$1:F1))),"")
 
Upvote 0
Thanks for the quick reply.
I guess it didn't work. The values that i get aren't the ones that shoud appear. Maybe i did something wrong.
But i can't use that method anyway because the processing time is too much just for a couple of cells and i need to paste that in hundreads of them.
Any other alternative?
 
Upvote 0
Thanks for the quick reply.
I guess it didn't work. The values that i get aren't the ones that shoud appear. Maybe i did something wrong.
But i can't use that method anyway because the processing time is too much just for a couple of cells and i need to paste that in hundreads of them.
Any other alternative?

In what way it didn't work?


xyzJAD JAD
qyzNAD WAD
xuzVAD
xuzZAD
quoOAD
pyoXAD
pyzQAD
puoLAD
quoKAD
xyzWAD

<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>
</TBODY>

F1, control+shift+enter and copy down:
Rich (BB code):
=IFERROR(INDEX($D$1:$D$100,SMALL(IF($A$1:$A$100="x",
  IF($B$1:$B$100="y",IF($C$1:$C$100="z",ROW($D$1:$D$100)-ROW($D$1)+1))),
  ROWS($F$1:F1))),"")
 
Upvote 0
Thanks for your effort.
I discovered that using sumifs and averageifs I get exactly what I need.
Thanks once again.
 
Upvote 0

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top