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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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 :
NAME</SPAN>
IPADDRESS</SPAN>
NAME_1</SPAN>
NAME_2</SPAN>
TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')</SPAN>
AVG(F.VALUEMAX)</SPAN>
AVG(F.VALUEMIN)</SPAN>
AVG(F.VALUESUM)</SPAN>
AVG(F.VALUECOUNT)</SPAN>
actual</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
CPU</SPAN>
CPUCpuUtil</SPAN>
2012-12-05 07:00</SPAN>
42.8256</SPAN>
3.17771</SPAN>
448.681</SPAN>
60</SPAN>
7.478021</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
FILESYSTEM</SPAN>
FSCapacity</SPAN>
2012-12-05 07:00</SPAN>
10.6667</SPAN>
10.6667</SPAN>
320</SPAN>
30</SPAN>
10.66667</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
MEMORY</SPAN>
MEMUsedMemPerc</SPAN>
2012-12-05 07:00</SPAN>
89.6787</SPAN>
88.7313</SPAN>
5349.52</SPAN>
60</SPAN>
89.15866</SPAN>

<TBODY>
</TBODY>



And need to ouput as per below:

CPUCpuUtil (actual)</SPAN>
FSCapacity (actual)</SPAN>
MEMUsedMemPerc (actual)</SPAN>
128.230.122.204</SPAN>

<TBODY>
</TBODY>
 
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 :
NAME
IPADDRESS
NAME_1
NAME_2
TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')
AVG(F.VALUEMAX)
AVG(F.VALUEMIN)
AVG(F.VALUESUM)
AVG(F.VALUECOUNT)
actual
Basic Admin
128.230.122.204
CPU
CPUCpuUtil
2012-12-05 07:00
42.8256
3.17771
448.681
60
7.478021
Basic Admin
128.230.122.204
FILESYSTEM
FSCapacity
2012-12-05 07:00
10.6667
10.6667
320
30
10.66667
Basic Admin
128.230.122.204
MEMORY
MEMUsedMemPerc
2012-12-05 07:00
89.6787
88.7313
5349.52
60
89.15866

<tbody>
</tbody>



And need to ouput as per below:

CPUCpuUtil (actual)
FSCapacity (actual)
MEMUsedMemPerc (actual)
128.230.122.204

<tbody>
</tbody>
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 :
NAME</SPAN>
IPADDRESS</SPAN>
NAME_1</SPAN>
NAME_2</SPAN>
TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')</SPAN>
AVG(F.VALUEMAX)</SPAN>
AVG(F.VALUEMIN)</SPAN>
AVG(F.VALUESUM)</SPAN>
AVG(F.VALUECOUNT)</SPAN>
actual</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
CPU</SPAN>
CPUCpuUtil</SPAN>
2012-12-05 07:00</SPAN>
42.8256</SPAN>
3.17771</SPAN>
448.681</SPAN>
60</SPAN>
7.478021</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
FILESYSTEM</SPAN>
FSCapacity</SPAN>
2012-12-05 07:00</SPAN>
10.6667</SPAN>
10.6667</SPAN>
320</SPAN>
30</SPAN>
10.66667</SPAN>
Basic Admin</SPAN>
128.230.122.204</SPAN>
MEMORY</SPAN>
MEMUsedMemPerc</SPAN>
2012-12-05 07:00</SPAN>
89.6787</SPAN>
88.7313</SPAN>
5349.52</SPAN>
60</SPAN>
89.15866</SPAN>

<TBODY>
</TBODY>



And need to ouput as per below:

CPUCpuUtil (actual)</SPAN>
FSCapacity (actual)</SPAN>
MEMUsedMemPerc (actual)</SPAN>
128.230.122.204</SPAN>

<TBODY>
</TBODY>
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.

NAMEIPADDRESSNAME_1NAME_2TO_CHAR(F.GMTTIMESTAMP,'YYYY-MM-DDHH24:MI')AVG(F.VALUEMAX)AVG(F.VALUEMIN)AVG(F.VALUESUM)AVG(F.VALUECOUNT)actual
Basic Admin128.230.122.204CPUCPUCpuUtil12/5/2012 7:0042.82563.17771448.681607.478021
Basic Admin128.230.122.204FILESYSTEMFSCapacity12/5/2012 7:0010.666710.66673203010.66667
Basic Admin128.230.122.204MEMORYMEMUsedMemPerc12/5/2012 7:0089.678788.73135349.526089.15866

<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>
</tbody>

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

CPUCpuUtilFSCapacityMEMUsedMemPerc
actualactualactual
128.230.122.2047.47802110.6666789.15866

<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>
</tbody>


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

P
  • Solved
Vlookup on Two matches
Replies
6
Views
129
Alex Blakenburg
A
excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
318
Fluff
Fluff
N
  • Question
MATCH and INDEX help
Replies
3
Views
182
etaf
etaf
A
  • Solved
Sumif using Multiple Criteria
Replies
4
Views
331
acool
A
A
  • Solved
Multiple Criteria in If function Returns error
Replies
8
Views
145
etaf
etaf

Forum statistics

Threads
1,215,884
Messages
6,127,567
Members
449,385
Latest member
KMGLarson

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