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

Col A

Col B

Col C

Col D

Col E

Col F

Col G

Col H

Col I

Col J

Col k

Col L

Row 1

Row 2

Data Set

Lookup Table

Row 3

Company

Type

Status

Days to complete

Status

Company

Type

Status

Min

Max

Status

Row 4

CO1

T1

Completed

120

=?

CO1

T1

Completed

61

100

Red

Row 5

CO2

T2

Completed

60

CO1

T1

Completed

31

60

Yellow

Row 6

CO2

T1

Cancelled

90

CO1

T1

Completed

0

30

Green

Row 7

CO1

T3

Cancelled

30

CO1

T1

Cancelled

46

100

Cancelled After 45

Row 8

CO2

T3

Completed

70

CO1

T1

Cancelled

16

45

Cancelled After 15

Row 9

CO1

T1

Cancelled

7

CO1

T1

Cancelled

0

15

Cancelled within 15

Row 10

CO1

T2

Completed

0

etc.

etc.

etc.

etc.

etc.

etc.

Row 11

etc.

etc.

etc.

etc.

Row 12

<tbody>
</tbody>

 

[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