lookup multiple criteria

[Maver]

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

Input	Age	25					Job code	Min age	max age	min pay	max pay	State
Input	State	IL					A123	10	50	100	100	TX,NY,IL,MN
Input	Job Code	B456					B456	20	30	50	100	NY,NJ
Output	Min Pay	50					B456	31	60	100	200	NY,NJ
Output	Max pay	100					B456	61	70	150	200	NY,NJ
Output	Valid	Not valid state					B456	71	80	170	201	NY,NJ
							C123	20	60	100	150	AR,TN
							D8910	30	90	40	200	AP,KY

<colgroup><col span="2"><col><col span="9"><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

How do you derive 50 for min pay from age = 25, state = IL and job code B456, given the data on data?

 

[Maver]

Hello Aladin,
since the age is 25 it falls under the 2nd row which is

B456

20

30

50

100

NY,NJ

<tbody>
</tbody>

 

[63falcondude]

Give these a try. I am making some assumptions.

Excel 2010
	A	B	C	D	E	F
1	Job code	Min age	max age	min pay	max pay	State
2	A123	10	50	100	100	TX,NY,IL,MN
3	B456	20	30	50	100	NY,NJ
4	B456	31	60	100	200	NY,NJ
5	B456	61	70	150	200	NY,NJ
6	B456	71	80	170	201	NY,NJ
7	C123	20	60	100	150	AR,TN
8	D8910	30	90	40	200	AP,KY
DATA SHEET

Excel 2010
	A	B	C
1	Input	Age	25
2	Input	State	IL
3	Input	Job Code	B456
4	Output	Min Pay	50
5	Output	Max pay	100
6	Output	Valid	Not Valid State
INPUT SHEET

Cell Formulas
Range	Formula
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")

 

[snjpverma]

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.

 

[Aladin Akyurek]

data

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

input

Row\Col	A​	B​	C​
1​	Input	Age	25​
2​	Input	State	IL
3​	Input	Job Code	B456
4​	Output	Min Pay	50​
5​	Output	Max pay	100​
6​	Output	Valid	Not valid state

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")

 

[Maver]

Give these a try. I am making some assumptions.

Excel 2010

	A	B	C	D	E	F
1	Job code	Min age	max age	min pay	max pay	State
2	A123	10	50	100	100	TX,NY,IL,MN
3	B456	20	30	50	100	NY,NJ
4	B456	31	60	100	200	NY,NJ
5	B456	61	70	150	200	NY,NJ
6	B456	71	80	170	201	NY,NJ
7	C123	20	60	100	150	AR,TN
8	D8910	30	90	40	200	AP,KY

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

DATA SHEET​

Excel 2010

	A	B	C
1	Input	Age	25
2	Input	State	IL
3	Input	Job Code	B456
4	Output	Min Pay	50
5	Output	Max pay	100
6	Output	Valid	Not Valid State

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

INPUT SHEET​

Worksheet Formulas Cell Formula 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") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanksalot@63falcondude. It is working.

 

[Maver]

Hi Aladin,
I am getting #n/a error, it worked once, but if I change the job code I am not getting any output

 

[Aladin Akyurek]

Hi Aladin,
I am getting #n/a error, it worked once, but if I change the job code I am not getting any output

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?