2 Way Lookup

[Wigman86]

Hello All

I am currently trying to do a 2 way lookup with unsorted data. So let's say that I want to return the value for where the value matches number 4 on my left column and G on the overhead row. So where 4 and G intersect return that value. In this case it would be 700. and the same for say where 5 and D intersect. The returned value here being 50. I hope that makes sense. Thanks in advance.

		A	B	C	D	E	F	G
1
2						14		8
3		100		6
4								700
5					50
6		11
7								9
8
9				12
10

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[DRSteele]

The use of function INDEX along with function MATCH is all you need.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2	Rows	Cols	Lookup
3	4	G	700
4	5	d	50
5						A	B	C	D	E	F	G
6				1
7				2						14		8
8				3		100		6
9				4								700
10				5					50
11				6		11
12				7								9
13				8
14				9				12
15				10
Sheet66

Cell Formulas
Range	Formula
C3	=INDEX($F$6:$L$15,MATCH(A3,$D$6:$D$15,0),MATCH(B3,$F$5:$L$5,0))

 

[Wigman86]

The use of function INDEX along with function MATCH is all you need.

	A	B	C	D	E	F	G	H	I	J	K	L
2	Rows	Cols	Lookup
3	4	G	700
4	5	d	50
5						A	B	C	D	E	F	G
6				1
7				2						14		8
8				3		100		6
9				4								700
10				5					50
11				6		11
12				7								9
13				8
14				9				12
15				10

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

Sheet66​

Worksheet Formulas Cell Formula C3 =INDEX($F$6:$L$15,MATCH(A3,$D$6:$D$15,0),MATCH(B3,$F$5:$L$5,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thank you DRSteele. This works great but in my case the data provided above was just an example. My data in column D of your table has multiple decimal signs and is specific. I am trying to return the value for 11.01.10.00 which you can see below is in column B and equals 12 however the formula keeps returning 10. I think this is because of the .00 but I cant find a work around for this as the number need to include the . symbols and stay the same. Thanks in advance for your help. Hope this makes sense.

A	B	C	D	E	F
11.01.10	10
11.01
11.01.10.00	12
11.01.12.22

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

 

[rtroehrig]

Using his spreadsheet layout and formula while changing the number format to match yours, I returned a value of 12. Make sure the number type format matches in both areas. I'm not sure why else you would not receive the desired value. I purposefully tried to return 10 and could not.