Three-way lookup in Excel table

[AnneDerks]

I have a table with three criteria. Two criteria are stated in columns, and one in a row.
The table looks like this:

I want to find a value which matches the three criteria.
For example: I want the value for 'Cembrit Cetris Basic' , SC1 and Permanent. Then a value of 0.3 (cell C3) should be the answer.

This is what I tried:
I tried to use INDEX MATCH to find a certain value. However, it does not work and I get N/A as answer. I tried many other possibilities, but I still cannot figure out how to lookup the value according to the three criteria.

=INDEX(C3:G12,MATCH(1,(Face_material=A3:A12)*(SC =B3:B12),0),MATCH(Load_duration,C2:G2,0))
And then pressing crtl+shift+enter. I gave names to the cells which should be looked up, so that is where the Face_material, SC, and Load_duration stands for.

Can someone please help me?
Thank you in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you could use something like this.

22 10 06.xlsm
	A	B	C	D	E	F	G
1
2			Permanent	Long	Medium	Short	Inst
3	a	SC1	0.84	0.6	0.94	0.44	0.91
4		SC2	0.69	0.22	0.34	0.6	0.7
5	b	SC1	0.59	0.21	0.95	0.1	0.91
6		SC2	0.63	0.24	0.81	0.78	0.95
7	c	SC1	0.63	0.83	0.1	0.67	0.92
8		SC2	0.15	0.4	0.2	0.93	0.05
9
10	b
11	SC2
12	Long
13	0.24
Lookup

Cell Formulas
Range	Formula
A13	A13	=INDEX(C3:G8,MATCH(A10,A3:A8,0)+(A11="SC2"),MATCH(A12,C2:G2,0))

 

[steve the fish]

Your major problem is the layout of the data.

 

[jasonb75]

Your major problem is the layout of the data.

The layout is not ideal, but it is workable. Peter has already provided a suggestion that works with consistency in the second column. Even without consistency it is still possible with something like this (borrowing Peter's mini sheet).

Book1
	A	B	C	D	E	F	G
1
2			Permanent	Long	Medium	Short	Inst
3	a	SC1	0.84	0.6	0.94	0.44	0.91
4		SC2	0.69	0.22	0.34	0.6	0.7
5	b	SC1	0.59	0.24	0.95	0.1	0.91
6		SC2	0.63	0.24	0.81	0.78	0.95
7	c	SC1	0.63	0.83	0.1	0.67	0.92
8		SC2	0.15	0.4	0.2	0.93	0.05
9
10	b
11	SC2
12	Long
13	0.24
Sheet1

Cell Formulas
Range	Formula
A13	A13	=LOOKUP(2,1/(IF(A3:A8="",A2:A7,A3:A8)=A10)/(B3:B8=A11),INDEX(C3:G8,,MATCH(A12,C2:G2,0)))

 

[AnneDerks]

The layout is not ideal, but it is workable. Peter has already provided a suggestion that works with consistency in the second column. Even without consistency it is still possible with something like this (borrowing Peter's mini sheet).

Book1
	A	B	C	D	E	F	G
1
2			Permanent	Long	Medium	Short	Inst
3	a	SC1	0.84	0.6	0.94	0.44	0.91
4		SC2	0.69	0.22	0.34	0.6	0.7
5	b	SC1	0.59	0.24	0.95	0.1	0.91
6		SC2	0.63	0.24	0.81	0.78	0.95
7	c	SC1	0.63	0.83	0.1	0.67	0.92
8		SC2	0.15	0.4	0.2	0.93	0.05
9
10	b
11	SC2
12	Long
13	0.24
Sheet1

Cell Formulas
Range	Formula
A13	A13	=LOOKUP(2,1/(IF(A3:A8="",A2:A7,A3:A8)=A10)/(B3:B8=A11),INDEX(C3:G8,,MATCH(A12,C2:G2,0)))

Thank you! However, when I now select SC2, it does not work. for SC1 it does work. But I also tried the solution of Peter_SSs and that one works so I think it is solved!

 

[AnneDerks]

Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you could use something like this.

22 10 06.xlsm
	A	B	C	D	E	F	G
1
2			Permanent	Long	Medium	Short	Inst
3	a	SC1	0.84	0.6	0.94	0.44	0.91
4		SC2	0.69	0.22	0.34	0.6	0.7
5	b	SC1	0.59	0.21	0.95	0.1	0.91
6		SC2	0.63	0.24	0.81	0.78	0.95
7	c	SC1	0.63	0.83	0.1	0.67	0.92
8		SC2	0.15	0.4	0.2	0.93	0.05
9
10	b
11	SC2
12	Long
13	0.24
Lookup

Cell Formulas
Range	Formula
A13	A13	=INDEX(C3:G8,MATCH(A10,A3:A8,0)+(A11="SC2"),MATCH(A12,C2:G2,0))

Thank you! This works indeed! I didn't know I could upload a mini Excel, next time I will do this, thank you!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

 

[jasonb75]

when I now select SC2, it does not work. for SC1 it does work.

It is possible that you didn't quite set it up correctly when you tried to apply the method to your actual sheet. Either that, or there is an inconsistency in the pattern of your data.

Whilst my suggestion works as well as Peter's with the example, I did not test it as thoroughly as I should have and there are still times where it will not work as I had intended. The main fail point (which will also apply to Peter's suggestion) would be a variation in the number of options for each material in the SC columns. Whilst both should work correctly with either 1 or 2 options in the SC column, having 3 or more options for any given material will cause them to fail.

As @steve the fish mentioned, the problem lies with the layout of the data. The empty cells in the materials column are not formula friendly. An ideal layout (and the way that most experienced users would set it up) would be with the material name entered into each row that it applies to as shown below, that way the formulas can validate the criteria correctly instead of having to make assumptions that may not always be correct.

		Permanent	Long	Medium	Short	Inst
a	SC1	0.84	1	0.94	0.44	0.91
a	SC2	0.69	2	0.34	0.6	0.7
b	SC1	0.59	3	0.95	0.1	0.91
b	SC2	0.63	4	0.81	0.78	0.95
c	SC1	0.63	5	0.1	0.67	0.92
c	SC2	0.15	6	0.2	0.93	0.05