VLOOKUP - Multiple Criteria

G

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.
 
Sort by date Sort by votes
Aladin Akyurek said:
Do you mean something like below?

G2: Sept
G3: Oct
Etc.

H1: 400
I1: 401
Etc.
Click to expand...

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

[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apl
[/TD]
[TD]May
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]10
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]40
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]20
[/TD]
[TD]40
[/TD]
[TD]60
[/TD]
[TD]80
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]30
[/TD]
[TD]60
[/TD]
[TD]90
[/TD]
[TD]120
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]40
[/TD]
[TD]80
[/TD]
[TD]120
[/TD]
[TD]160
[/TD]
[TD]200
[/TD]
[/TR]
</tbody>[/TABLE]


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

PLEASE AND THANK YOU!!
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Kyosti said:
YES!!!! So here is a small example:

[TABLE="class: grid, width: 150"]
<TBODY>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apl
[/TD]
[TD]May
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]10
[/TD]
[TD]20
[/TD]
[TD]30
[/TD]
[TD]40
[/TD]
[TD]50
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]20
[/TD]
[TD]40
[/TD]
[TD]60
[/TD]
[TD]80
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]30
[/TD]
[TD]60
[/TD]
[TD]90
[/TD]
[TD]120
[/TD]
[TD]150
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]40
[/TD]
[TD]80
[/TD]
[TD]120
[/TD]
[TD]160
[/TD]
[TD]200
[/TD]
[/TR]
</TBODY>[/TABLE]


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

PLEASE AND THANK YOU!!
Click to expand...

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
OneTwo said:
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.
Click to expand...

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
OneTwo said:
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?
Click to expand...

In what way it didn't work?


[TABLE="width: 288"]
<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]x[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]y[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]z[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]JAD[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]JAD[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]q[/TD]
[TD="class: xl63, bgcolor: transparent"]y[/TD]
[TD="class: xl63, bgcolor: transparent"]z[/TD]
[TD="class: xl63, bgcolor: transparent"]NAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]WAD[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]x[/TD]
[TD="class: xl63, bgcolor: transparent"]u[/TD]
[TD="class: xl63, bgcolor: transparent"]z[/TD]
[TD="class: xl63, bgcolor: transparent"]VAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]x[/TD]
[TD="class: xl63, bgcolor: transparent"]u[/TD]
[TD="class: xl63, bgcolor: transparent"]z[/TD]
[TD="class: xl63, bgcolor: transparent"]ZAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]q[/TD]
[TD="class: xl63, bgcolor: transparent"]u[/TD]
[TD="class: xl63, bgcolor: transparent"]o[/TD]
[TD="class: xl63, bgcolor: transparent"]OAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]p[/TD]
[TD="class: xl63, bgcolor: transparent"]y[/TD]
[TD="class: xl63, bgcolor: transparent"]o[/TD]
[TD="class: xl63, bgcolor: transparent"]XAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]p[/TD]
[TD="class: xl63, bgcolor: transparent"]y[/TD]
[TD="class: xl63, bgcolor: transparent"]z[/TD]
[TD="class: xl63, bgcolor: transparent"]QAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]p[/TD]
[TD="class: xl63, bgcolor: transparent"]u[/TD]
[TD="class: xl63, bgcolor: transparent"]o[/TD]
[TD="class: xl63, bgcolor: transparent"]LAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]q[/TD]
[TD="class: xl63, bgcolor: transparent"]u[/TD]
[TD="class: xl63, bgcolor: transparent"]o[/TD]
[TD="class: xl63, bgcolor: transparent"]KAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]x[/TD]
[TD="class: xl63, bgcolor: transparent"]y[/TD]
[TD="class: xl63, bgcolor: transparent"]z[/TD]
[TD="class: xl63, bgcolor: transparent"]WAD[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
197
Rokcmd
R
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
628
Peter_SSs
Peter_SSs
C
  • Question Question
How to show a list view of columns based off another sheet / alternative to Pivot table?
Replies
7
Views
129
Domenic
D
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
590
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,846
Messages
6,181,304
Members
453,031
Latest member
Chris_1

Share this page

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