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
I will definitely refer to the workbook you cited. There remains much for me to learn in Excel.

However, IT WORKS NOW!
I did not comprehend the importance of the lookup table you called Categories. Turns out it is a counting index (sure there is a more professional term). Once I fixed that table to contain the recurring values of B in Sheet1 in order, the deed was done.
Still, that single quote and following spaces do foul things up a bit. Turns out they are just there to cause an indent in the text. Would really like to clear them out.

Man this is exciting. Instead of going through all the hassle of getting finance to make meaningful reports, I can just make 'em myself. You folks are awesome. Thanks for the help.

Rockfish
PS: And thanks to Ron who turned me on to this site 2 years ago.
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Rockfish said:
I will definitely refer to the workbook you cited. There remains much for me to learn in Excel.

However, IT WORKS NOW!
I did not comprehend the importance of the lookup table you called Categories. Turns out it is a counting index (sure there is a more professional term). Once I fixed that table to contain the recurring values of B in Sheet1 in order, the deed was done.
Still, that single quote and following spaces do foul things up a bit. Turns out they are just there to cause an indent in the text. Would really like to clear them out.

Man this is exciting. Instead of going through all the hassle of getting finance to make meaningful reports, I can just make 'em myself. You folks are awesome. Thanks for the help.

Rockfish
PS: And thanks to Ron who turned me on to this site 2 years ago.
Click to expand...

Great. Thanks for providing feedback.
 
Upvote 0
I have a question, what function should i use if i have the below source (portion snapshot of it). Tried using lookup function but couldnt really figure out how to proceed :
[TABLE="width: 828"]
<TBODY>[TR]
[TD]NAME</SPAN>
[/TD]
[TD]IPADDRESS</SPAN>
[/TD]
[TD]NAME_1</SPAN>
[/TD]
[TD]NAME_2</SPAN>
[/TD]
[TD]TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')</SPAN>
[/TD]
[TD]AVG(F.VALUEMAX)</SPAN>
[/TD]
[TD]AVG(F.VALUEMIN)</SPAN>
[/TD]
[TD]AVG(F.VALUESUM)</SPAN>
[/TD]
[TD]AVG(F.VALUECOUNT)</SPAN>
[/TD]
[TD]actual</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]CPU</SPAN>
[/TD]
[TD]CPUCpuUtil</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]42.8256</SPAN>
[/TD]
[TD]3.17771</SPAN>
[/TD]
[TD]448.681</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD="align: right"]7.478021</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]FILESYSTEM</SPAN>
[/TD]
[TD]FSCapacity</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]10.6667</SPAN>
[/TD]
[TD]10.6667</SPAN>
[/TD]
[TD]320</SPAN>
[/TD]
[TD]30</SPAN>
[/TD]
[TD="align: right"]10.66667</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]MEMORY</SPAN>
[/TD]
[TD]MEMUsedMemPerc</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]89.6787</SPAN>
[/TD]
[TD]88.7313</SPAN>
[/TD]
[TD]5349.52</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD="align: right"]89.15866</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]



And need to ouput as per below:

[TABLE="width: 528"]
<TBODY>[TR]
[TD][/TD]
[TD]CPUCpuUtil (actual)</SPAN>
[/TD]
[TD]FSCapacity (actual)</SPAN>
[/TD]
[TD]MEMUsedMemPerc (actual)</SPAN>
[/TD]
[/TR]
[TR]
[TD]128.230.122.204</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
dixonleong said:
I have a question, what function should i use if i have the below source (portion snapshot of it). Tried using lookup function but couldnt really figure out how to proceed :
[TABLE="width: 828"]
<tbody>[TR]
[TD]NAME
[/TD]
[TD]IPADDRESS
[/TD]
[TD]NAME_1
[/TD]
[TD]NAME_2
[/TD]
[TD]TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')
[/TD]
[TD]AVG(F.VALUEMAX)
[/TD]
[TD]AVG(F.VALUEMIN)
[/TD]
[TD]AVG(F.VALUESUM)
[/TD]
[TD]AVG(F.VALUECOUNT)
[/TD]
[TD]actual
[/TD]
[/TR]
[TR]
[TD]Basic Admin
[/TD]
[TD]128.230.122.204
[/TD]
[TD]CPU
[/TD]
[TD]CPUCpuUtil
[/TD]
[TD]2012-12-05 07:00
[/TD]
[TD]42.8256
[/TD]
[TD]3.17771
[/TD]
[TD]448.681
[/TD]
[TD]60
[/TD]
[TD="align: right"]7.478021
[/TD]
[/TR]
[TR]
[TD]Basic Admin
[/TD]
[TD]128.230.122.204
[/TD]
[TD]FILESYSTEM
[/TD]
[TD]FSCapacity
[/TD]
[TD]2012-12-05 07:00
[/TD]
[TD]10.6667
[/TD]
[TD]10.6667
[/TD]
[TD]320
[/TD]
[TD]30
[/TD]
[TD="align: right"]10.66667
[/TD]
[/TR]
[TR]
[TD]Basic Admin
[/TD]
[TD]128.230.122.204
[/TD]
[TD]MEMORY
[/TD]
[TD]MEMUsedMemPerc
[/TD]
[TD]2012-12-05 07:00
[/TD]
[TD]89.6787
[/TD]
[TD]88.7313
[/TD]
[TD]5349.52
[/TD]
[TD]60
[/TD]
[TD="align: right"]89.15866
[/TD]
[/TR]
</tbody>[/TABLE]



And need to ouput as per below:

[TABLE="width: 528"]
<tbody>[TR]
[TD][/TD]
[TD]CPUCpuUtil (actual)
[/TD]
[TD]FSCapacity (actual)
[/TD]
[TD]MEMUsedMemPerc (actual)
[/TD]
[/TR]
[TR]
[TD]128.230.122.204
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Sheet1, A3: 128.230.122.204

Sheet1, B1: CPUCpuUtil

Sheet1, B2 : Actual [ no parens around ]

C1:C2 and D1:D2 are patterned the same way.

Sheet2 houses the data in A1:J5 with A1:I2 filled with headers. J1 contains the header field "actual".

Sheet1, B3, control+shift+enter, not just enter, and copied across:
Rich (BB code): 
=INDEX(Sheet2!$B$3:$I$5,
  MATCH(1,IF(Sheet2!$B$3:$B$5=$A3,IF(Sheet2!$D$3:$D$5=B$1,1)),0),
  MATCH(B$2,Sheet2!$B$1:$J$1,0))
 
Upvote 0
dixonleong said:
I have a question, what function should i use if i have the below source (portion snapshot of it). Tried using lookup function but couldnt really figure out how to proceed :
[TABLE="width: 828"]
<TBODY>[TR]
[TD]NAME</SPAN>
[/TD]
[TD]IPADDRESS</SPAN>
[/TD]
[TD]NAME_1</SPAN>
[/TD]
[TD]NAME_2</SPAN>
[/TD]
[TD]TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')</SPAN>
[/TD]
[TD]AVG(F.VALUEMAX)</SPAN>
[/TD]
[TD]AVG(F.VALUEMIN)</SPAN>
[/TD]
[TD]AVG(F.VALUESUM)</SPAN>
[/TD]
[TD]AVG(F.VALUECOUNT)</SPAN>
[/TD]
[TD]actual</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]CPU</SPAN>
[/TD]
[TD]CPUCpuUtil</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]42.8256</SPAN>
[/TD]
[TD]3.17771</SPAN>
[/TD]
[TD]448.681</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD="align: right"]7.478021</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]FILESYSTEM</SPAN>
[/TD]
[TD]FSCapacity</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]10.6667</SPAN>
[/TD]
[TD]10.6667</SPAN>
[/TD]
[TD]320</SPAN>
[/TD]
[TD]30</SPAN>
[/TD]
[TD="align: right"]10.66667</SPAN>
[/TD]
[/TR]
[TR]
[TD]Basic Admin</SPAN>
[/TD]
[TD]128.230.122.204</SPAN>
[/TD]
[TD]MEMORY</SPAN>
[/TD]
[TD]MEMUsedMemPerc</SPAN>
[/TD]
[TD]2012-12-05 07:00</SPAN>
[/TD]
[TD]89.6787</SPAN>
[/TD]
[TD]88.7313</SPAN>
[/TD]
[TD]5349.52</SPAN>
[/TD]
[TD]60</SPAN>
[/TD]
[TD="align: right"]89.15866</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]



And need to ouput as per below:

[TABLE="width: 528"]
<TBODY>[TR]
[TD][/TD]
[TD]CPUCpuUtil (actual)</SPAN>
[/TD]
[TD]FSCapacity (actual)</SPAN>
[/TD]
[TD]MEMUsedMemPerc (actual)</SPAN>
[/TD]
[/TR]
[TR]
[TD]128.230.122.204</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
If your data is sorted as it appears to be...

Data on Sheet1 in the range A2:J4.

Summary on Sheet2.

A2 = 128.230.122.204

Enter this formula in B2 and copy across to D2:

=INDEX($J$2:$J$4,MATCH($A11,$B$2:$B$4,0)+COLUMNS($B11:B11)-1)
</SPAN>
 
Upvote 0
T. Valko said:
If your data is sorted as it appears to be...

Data on Sheet1 in the range A2:J4.

Summary on Sheet2.

A2 = 128.230.122.204

Enter this formula in B2 and copy across to D2:

=INDEX($J$2:$J$4,MATCH($A11,$B$2:$B$4,0)+COLUMNS($B11:B11)-1)
</SPAN>
Click to expand...
Ooops!

I forgot to include the sheet name.

Entered on Sheet2 in cell B2 and copied across to D2:

=INDEX(Sheet1!$J$2:$J$4,MATCH($A2,Sheet1!$B$2:$B$4,0)+COLUMNS($B2:B2)-1)
 
Upvote 0
Aladin Akyurek said:
Sheet1, A3: 128.230.122.204

Sheet1, B1: CPUCpuUtil

Sheet1, B2 : Actual [ no parens around ]

C1:C2 and D1:D2 are patterned the same way.

Sheet2 houses the data in A1:J5 with A1:I2 filled with headers. J1 contains the header field "actual".

Sheet1, B3, control+shift+enter, not just enter, and copied across:
Rich (BB code): 
=INDEX(Sheet2!$B$3:$I$5,
  MATCH(1,IF(Sheet2!$B$3:$B$5=$A3,IF(Sheet2!$D$3:$D$5=B$1,1)),0),
  MATCH(B$2,Sheet2!$B$1:$J$1,0))
Click to expand...

Re-cap with exhibits...

Sheet2, A1:J4, houses the data, with the headers in A1:J1.

[TABLE="width: 1297"]
<colgroup><col style="width: 92pt; mso-width-source: userset; mso-width-alt: 4380;" width="123"> <col style="width: 122pt; mso-width-source: userset; mso-width-alt: 5774;" width="162"> <col style="width: 89pt; mso-width-source: userset; mso-width-alt: 4209;" width="118"> <col style="width: 110pt; mso-width-source: userset; mso-width-alt: 5205;" width="146"> <col style="width: 292pt; mso-width-source: userset; mso-width-alt: 13824;" width="389"> <col style="width: 107pt; mso-width-source: userset; mso-width-alt: 5063;" width="142"> <col style="width: 115pt; mso-width-source: userset; mso-width-alt: 5432;" width="153"> <col style="width: 143pt; mso-width-source: userset; mso-width-alt: 6798;" width="191"> <col style="width: 169pt; mso-width-source: userset; mso-width-alt: 8021;" width="226"> <col style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;" width="77"> <tbody>[TR]
[TD="class: xl65, width: 123, bgcolor: white"]NAME[/TD]
[TD="class: xl65, width: 162, bgcolor: white"]IPADDRESS[/TD]
[TD="class: xl65, width: 118, bgcolor: white"]NAME_1[/TD]
[TD="class: xl65, width: 146, bgcolor: white"]NAME_2[/TD]
[TD="class: xl66, width: 389, bgcolor: white"]TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')[/TD]
[TD="class: xl65, width: 142, bgcolor: white"]AVG(F.VALUEMAX)[/TD]
[TD="class: xl65, width: 153, bgcolor: white"]AVG(F.VALUEMIN)[/TD]
[TD="class: xl65, width: 191, bgcolor: white"]AVG(F.VALUESUM)[/TD]
[TD="class: xl65, width: 226, bgcolor: white"]AVG(F.VALUECOUNT)[/TD]
[TD="class: xl67, width: 77, bgcolor: white"]actual[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]Basic Admin[/TD]
[TD="class: xl65, width: 162, bgcolor: white"]128.230.122.204[/TD]
[TD="class: xl65, width: 118, bgcolor: white"]CPU[/TD]
[TD="class: xl65, width: 146, bgcolor: white"]CPUCpuUtil[/TD]
[TD="class: xl66, width: 389, bgcolor: white"]12/5/2012 7:00[/TD]
[TD="class: xl65, width: 142, bgcolor: white"]42.8256[/TD]
[TD="class: xl65, width: 153, bgcolor: white"]3.17771[/TD]
[TD="class: xl65, width: 191, bgcolor: white"]448.681[/TD]
[TD="class: xl65, width: 226, bgcolor: white"]60[/TD]
[TD="class: xl67, width: 77, bgcolor: white"]7.478021[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]Basic Admin[/TD]
[TD="class: xl65, width: 162, bgcolor: white"]128.230.122.204[/TD]
[TD="class: xl65, width: 118, bgcolor: white"]FILESYSTEM[/TD]
[TD="class: xl65, width: 146, bgcolor: white"]FSCapacity[/TD]
[TD="class: xl66, width: 389, bgcolor: white"]12/5/2012 7:00[/TD]
[TD="class: xl65, width: 142, bgcolor: white"]10.6667[/TD]
[TD="class: xl65, width: 153, bgcolor: white"]10.6667[/TD]
[TD="class: xl65, width: 191, bgcolor: white"]320[/TD]
[TD="class: xl65, width: 226, bgcolor: white"]30[/TD]
[TD="class: xl67, width: 77, bgcolor: white"]10.66667[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 123, bgcolor: white"]Basic Admin[/TD]
[TD="class: xl65, width: 162, bgcolor: white"]128.230.122.204[/TD]
[TD="class: xl65, width: 118, bgcolor: white"]MEMORY[/TD]
[TD="class: xl65, width: 146, bgcolor: white"]MEMUsedMemPerc[/TD]
[TD="class: xl66, width: 389, bgcolor: white"]12/5/2012 7:00[/TD]
[TD="class: xl65, width: 142, bgcolor: white"]89.6787[/TD]
[TD="class: xl65, width: 153, bgcolor: white"]88.7313[/TD]
[TD="class: xl65, width: 191, bgcolor: white"]5349.52[/TD]
[TD="class: xl65, width: 226, bgcolor: white"]60[/TD]
[TD="class: xl67, width: 77, bgcolor: white"]89.15866[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1, A:D, houses the processing...

[TABLE="width: 349"]
<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4664;" width="131"> <col style="width: 79pt; mso-width-source: userset; mso-width-alt: 3726;" width="105"> <col style="width: 65pt; mso-width-source: userset; mso-width-alt: 3100;" width="87"> <col style="width: 107pt; mso-width-source: userset; mso-width-alt: 5091;" width="143"> <tbody>[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 105, bgcolor: transparent"]CPUCpuUtil[/TD]
[TD="class: xl66, width: 87, bgcolor: transparent"]FSCapacity[/TD]
[TD="class: xl66, width: 143, bgcolor: transparent"]MEMUsedMemPerc[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 105, bgcolor: transparent"]actual[/TD]
[TD="class: xl66, width: 87, bgcolor: transparent"]actual[/TD]
[TD="class: xl66, width: 143, bgcolor: transparent"]actual[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"]128.230.122.204[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]7.478021[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10.66667[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]89.15866[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


B3, control+shift+enter, not just enter, and copied across:
Rich (BB code): 
=INDEX(Sheet2!$B$2:$J$4,
   MATCH(1,IF(Sheet2!$B$2:$B$4=$A3,IF(Sheet2!$D$2:$D$4=B$1,1)),0),
   MATCH(B$2,Sheet2!$B$1:$J$1,0))
 
Upvote 0
Aladin Akyurek said:
B3, control+shift+enter, not just enter, and copied across:

=INDEX(Sheet2!$B$2:$J$4,
MATCH(1,IF(Sheet2!$B$2:$B$4=$A3,IF(Sheet2!$D$2:$D$4=B$1,1)),0),
MATCH(B$2,Sheet2!$B$1:$J$1,0))
Click to expand...
That can be reduced to:

=INDEX(Sheet2!$B$2:$J$4,MATCH(B$1,IF(Sheet2!$B$2:$B$4=$A3,Sheet2!$D$2:$D$4),0),MATCH(B$2,Sheet2!$B$1:$J$1,0))
 
Upvote 0
New to the site and need some assistance with this function. I've got it all down and working, except if the two criteria are not met, how do I make the formula return a zero instead of #N/A. Is that possible with this function?

=INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0))

Thanks in advance.
 
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,847
Messages
6,181,354
Members
453,033
Latest member
lapmangviettel

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