Multi Criteria Lookup: Some criteria are text and one is a range of Numbers

[NotoriousTAT]

Hello,
I am trying to create a lookup of some sort that has 4 criteria (3 are text and 1 a range of numbers). The result of the lookup will be text and ideally without the need for additional concatenation columns if possible. The last criteria is a range of numbers that is unique to the company and status criteria. I havent figured out how to get this done without nested if statements. Any advice or thoughts would be great. Trying to expand my toolbox of excel knowledge. Thanks

[TABLE="width: 833"]
<tbody>[TR]
[TD][/TD]
[TD]Col A[/TD]
[TD]Col B[/TD]
[TD]Col C[/TD]
[TD]Col D[/TD]
[TD]Col E[/TD]
[TD]Col F[/TD]
[TD]Col G[/TD]
[TD]Col H[/TD]
[TD]Col I[/TD]
[TD]Col J[/TD]
[TD]Col k[/TD]
[TD]Col L[/TD]
[/TR]
[TR]
[TD]Row 1[/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]
[TR]
[TD]Row 2[/TD]
[TD]Data Set[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lookup Table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]Company[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Days to complete[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD]Company[/TD]
[TD]Type[/TD]
[TD]Status[/TD]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Row 4[/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Completed[/TD]
[TD]120[/TD]
[TD]=?[/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Completed[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]100[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Row 5[/TD]
[TD]CO2[/TD]
[TD]T2[/TD]
[TD]Completed[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Completed[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]60[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Row 6[/TD]
[TD]CO2[/TD]
[TD]T1[/TD]
[TD]Cancelled[/TD]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Completed[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Row 7[/TD]
[TD]CO1[/TD]
[TD]T3[/TD]
[TD]Cancelled[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]100[/TD]
[TD]Cancelled After 45[/TD]
[/TR]
[TR]
[TD]Row 8[/TD]
[TD]CO2[/TD]
[TD]T3[/TD]
[TD]Completed[/TD]
[TD]70[/TD]
[TD][/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]45[/TD]
[TD]Cancelled After 15[/TD]
[/TR]
[TR]
[TD]Row 9[/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Cancelled[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]CO1[/TD]
[TD]T1[/TD]
[TD]Cancelled[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[TD]Cancelled within 15[/TD]
[/TR]
[TR]
[TD]Row 10[/TD]
[TD]CO1[/TD]
[TD]T2[/TD]
[TD]Completed[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[/TR]
[TR]
[TD]Row 11[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD]etc.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row 12[/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]

 

[Fazza]

Maybe a query is OK for you?

UPDATE DataSet D INNER JOIN LookupTable L ON D.Company = L.Company AND D.Type = L.Type
SET D.Status = L.Status
WHERE D.[Days to complete] >= L.[Min] AND D.[Days to complete] <= L.[Max]

Untested.

This is a non-formula database type approach, especially good for large datasets. It can be set up manually via ALT-D-D-N and follow the wizard, or various ways using VBA.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you are after?

If not, can you provide the expected results for that sample data or provide another set of sample data, including the expected results with any further clarification?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Data Set						Lookup Table
3	Company	Type	Status	Days to complete	Status		Company	Type	Status	Min	Max	Status
4	CO1	T1	Completed	120	Unknown		CO1	T1	Completed	61	100	Red
5	CO2	T2	Completed	60	Unknown		CO1	T1	Completed	31	60	Yellow
6	CO2	T1	Cancelled	90	Unknown		CO1	T1	Completed	0	30	Green
7	CO1	T3	Cancelled	30	Unknown		CO1	T1	Cancelled	46	100	Cancelled After 45
8	CO2	T3	Completed	70	Unknown		CO1	T1	Cancelled	16	45	Cancelled After 15
9	CO1	T1	Cancelled	7	Cancelled within 15		CO1	T1	Cancelled	0	15	Cancelled within 15
Status