lookup multiple criteria

M

Maver

New Member
Joined
Jan 20, 2017
Messages
13
I am trying to find a way to look up min/max pay from data sheet if it matches the job code & age in the in the input sheet. I tried index & match function but it didn't work for me.


  • If I give input as B456 & age 25 then output should be 50,100 & not valid state.
    If the age is 81 then output will be doesn't exist..
    Please find attached sample data







INPUT SHEET DATA SHEET
[TABLE="width: 894"]
<colgroup><col span="2"><col><col span="9"><col></colgroup><tbody>[TR]
[TD]Input[/TD]
[TD]Age[/TD]
[TD="align: right"]25[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Job code[/TD]
[TD]Min age[/TD]
[TD]max age[/TD]
[TD]min pay[/TD]
[TD]max pay[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]Input[/TD]
[TD]State[/TD]
[TD]IL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]A123[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]
[TD]TX,NY,IL,MN[/TD]
[/TR]
[TR]
[TD]Input[/TD]
[TD]Job Code[/TD]
[TD]B456[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B456[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD]NY,NJ[/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD]Min Pay[/TD]
[TD="align: right"]50[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B456[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD]NY,NJ[/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD]Max pay[/TD]
[TD="align: right"]100[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B456[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[TD]NY,NJ[/TD]
[/TR]
[TR]
[TD]Output[/TD]
[TD]Valid[/TD]
[TD]Not valid state[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]B456[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]201[/TD]
[TD]NY,NJ[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]C123[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD]AR,TN[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]D8910[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]200[/TD]
[TD]AP,KY[/TD]
[/TR]
[TR]
[TD]



[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Hello Aladin,
since the age is 25 it falls under the 2nd row which is

[TABLE="class: cms_table, width: 894"]
<tbody>[TR]
[TD]B456[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD]NY,NJ[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Give these a try. I am making some assumptions.


Excel 2010
ABCDEF
1Job codeMin agemax agemin paymax payState
2A1231050100100TX,NY,IL,MN
3B456203050100NY,NJ
4B4563160100200NY,NJ
5B4566170150200NY,NJ
6B4567180170201NY,NJ
7C1232060100150AR,TN
8D8910309040200AP,KY
DATA SHEET



Excel 2010
ABC
1InputAge25
2InputStateIL
3InputJob CodeB456
4OutputMin Pay50
5OutputMax pay100
6OutputValidNot Valid State
INPUT SHEET
Cell Formulas
RangeFormula
C4=SUMIFS('DATA SHEET'!D:D,'DATA SHEET'!B:B,"<="&C1,'DATA SHEET'!C:C,">="&C1,'DATA SHEET'!A:A,C3)
C5=SUMIFS('DATA SHEET'!E:E,'DATA SHEET'!B:B,"<="&C1,'DATA SHEET'!C:C,">="&C1,'DATA SHEET'!A:A,C3)
C6=IF(COUNTIFS('DATA SHEET'!A:A,C3,'DATA SHEET'!F:F,"*"&C2&"*"),"Valid State","Not Valid State")
 
Upvote 0
hi Falcon, I was trying hard to work this out with a sumproduct formula but it didn't work. Could you please show me how to get (either max pay or min Pay) using Sumproduct ? i.e. ur formula in C4 or C5.
 
Upvote 0
data

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][/tr][tr][td]
1​
[/td][td] Job code[/td][td] Min age[/td][td] max age[/td][td] min pay[/td][td] max pay[/td][td] State[/td][/tr]
[tr][td]
2​
[/td][td] A123[/td][td]
10​
[/td][td]
50​
[/td][td]
100​
[/td][td]
100​
[/td][td] TX,NY,IL,MN[/td][/tr]
[tr][td]
3​
[/td][td] B456[/td][td]
20​
[/td][td]
30​
[/td][td]
50​
[/td][td]
100​
[/td][td] NY,NJ[/td][/tr]
[tr][td]
4​
[/td][td] B456[/td][td]
31​
[/td][td]
60​
[/td][td]
100​
[/td][td]
200​
[/td][td] NY,NJ[/td][/tr]
[tr][td]
5​
[/td][td] B456[/td][td]
61​
[/td][td]
70​
[/td][td]
150​
[/td][td]
200​
[/td][td] NY,NJ[/td][/tr]
[tr][td]
6​
[/td][td] B456[/td][td]
71​
[/td][td]
80​
[/td][td]
170​
[/td][td]
201​
[/td][td] NY,NJ[/td][/tr]
[tr][td]
7​
[/td][td] C123[/td][td]
20​
[/td][td]
60​
[/td][td]
100​
[/td][td]
150​
[/td][td] AR,TN[/td][/tr]
[tr][td]
8​
[/td][td] D8910[/td][td]
30​
[/td][td]
90​
[/td][td]
40​
[/td][td]
200​
[/td][td] AP,KY[/td][/tr]
[/table]

input

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][/tr][tr][td]
1​
[/td][td] Input[/td][td] Age[/td][td]
25​
[/td][/tr]
[tr][td]
2​
[/td][td] Input[/td][td] State[/td][td] IL[/td][/tr]
[tr][td]
3​
[/td][td] Input[/td][td] Job Code[/td][td] B456[/td][/tr]
[tr][td]
4​
[/td][td] Output[/td][td] Min Pay[/td][td]
50​
[/td][/tr]
[tr][td]
5​
[/td][td] Output[/td][td] Max pay[/td][td]
100​
[/td][/tr]
[tr][td]
6​
[/td][td] Output[/td][td] Valid[/td][td] Not valid state[/td][/tr]
[/table]

In C4 control+shift+enter, not just enter:

=LOOKUP(C1,IF(data!$A$2:$A$8=C3,IF(C1>=data!$B$2:$B$8,IF(C1<=data!$C$3:$C$8,data!$B$2:$B$8))),data!$D$2:$D$8)

In C5 control+shift+enter, not just enter:

=LOOKUP(C1,IF(data!$A$2:$A$8=C3,IF(C1>=data!$B$2:$B$8,IF(C1<=data!$C$3:$C$8,data!$B$2:$B$8))),data!$E$2:$E$8)

In C6 control+shift+enter, not just enter:

=IF(ISNUMBER(SEARCH(","&C2&",",","&LOOKUP(C1,IF(data!$A$2:$A$8=C3,IF(C1>=data!$B$2:$B$8,IF(C1<=data!$C$3:$C$8,data!$B$2:$B$8))),data!$F$2:$F$8)&",")),"valid state","not valid state")
 
Upvote 0
63falcondude said:
Give these a try. I am making some assumptions.

Excel 2010
ABCDEF
Job codeMin agemax agemin paymax payState
A123TX,NY,IL,MN
B456NY,NJ
B456NY,NJ
B456NY,NJ
B456NY,NJ
C123AR,TN
D8910AP,KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]31[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]61[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]71[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]201[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]30[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]200[/TD]

</tbody>
DATA SHEET​



Excel 2010
ABC
InputAge
InputStateIL
InputJob CodeB456
OutputMin Pay
OutputMax pay
OutputValidNot Valid State

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]25[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]50[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]6[/TD]

</tbody>
INPUT SHEET​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C4[/TH]
[TD="align: left"]=SUMIFS('DATA SHEET'!D:D,'DATA SHEET'!B:B,"<="&C1,'DATA SHEET'!C:C,">="&C1,'DATA SHEET'!A:A,C3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=SUMIFS('DATA SHEET'!E:E,'DATA SHEET'!B:B,"<="&C1,'DATA SHEET'!C:C,">="&C1,'DATA SHEET'!A:A,C3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=IF(COUNTIFS('DATA SHEET'!A:A,C3,'DATA SHEET'!F:F,"*"&C2&"*"),"Valid State","Not Valid State")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Thanksalot@63falcondude. It is working.
 
Upvote 0
Hi Aladin,
I am getting #n/a error, it worked once, but if I change the job code I am not getting any output
 
Upvote 0
Maver said:
Hi Aladin,
I am getting #n/a error, it worked once, but if I change the job code I am not getting any output
Click to expand...

If you change C3, the job code, to: D8910, you will get correctly #N/A for Min Pay and Max Pay. If you want to suppress an error result, wrap the formulas in C4 and C5 into an IFNA or IFERROR call.

=IFNA(LOOKUP(C1,IF(data!$A$2:$A$8=C3,IF(C1>=data!$B$2:$B$8,IF(C1<=data!$C$3:$C$8,data!$B$2:$B$8))),data!$D$2:$D$8),"not available")

=IFNA(LOOKUP(C1,IF(data!$A$2:$A$8=C3,IF(C1>=data!$B$2:$B$8,IF(C1<=data!$C$3:$C$8,data!$B$2:$B$8))),data!$E$2:$E$8),"not available")

Recall confirming these formulas with control+shift+enter, also after an edit.

Does this extension meet what you need?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA for setting up the printing pages per sheet
Replies
0
Views
769
Aurum1
A
N
  • Solved
VBA auto fill in information from the list in one sheet to another sheet and save as the excel file.
Replies
5
Views
290
ngochien251088
N
L
  • Solved
INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria
Replies
3
Views
529
Fluff
Fluff
A
  • Solved
INDEX MATCH or similar with multiple criteria?
Replies
3
Views
441
Fluff
Fluff
H
  • Solved
VBA to consolidate list
Replies
7
Views
598
hwong8848
H

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