Lookup in a matrix?

[Gids007]

Hi There,

I want to return a number based on a horizontal and a vertical lookup. Here's the lookup table...

	NI	SI
Beef Cattle Farming	9​	9​
Dairy Cattle Farming	12.5​	15.5​
Grain-Sheep and Grain-Beef Cattle Farming	9​	9​
Sheep Farming	5.5​	5.5​
Sheep-Beef Cattle Farming	5.5​	5.5​

I have 2 columns within a dataset....below...I want a formula to return 15.5 in the lookup column....There are 100 rows of data with different combinations of Legal Entity and NISI...any help appreciated...

Legal Entity Industry Name	NISI	lookup
Dairy Cattle Farming	SI
Dairy Cattle Farming	SI
Dairy Cattle Farming	SI
Dairy Cattle Farming	SI
Dairy Cattle Farming	SI

 

[bebo021999]

Try INDEX(range,row_index, column_index)
with row and column _ index, using MATCH function.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If there are only 2 choices for the column to look in you could also try this. (I have changed some of the sample data)

22 06 27.xlsm
	A	B	C
1		NI	SI
2	Beef Cattle Farming	9	9
3	Dairy Cattle Farming	12.5	15.5
4	Grain-Sheep and Grain-Beef Cattle Farming	8.2	9
5	Sheep Farming	5.5	6
6	Sheep-Beef Cattle Farming	5.5	7
7
8
9	Legal Entity Industry Name	NISI	lookup
10	Dairy Cattle Farming	SI	15.5
11	Sheep Farming	NI	5.5
12	Sheep-Beef Cattle Farming	SI	7
13	Grain-Sheep and Grain-Beef Cattle Farming	SI	9
14	Dairy Cattle Farming	NI	12.5
Lookup

Cell Formulas
Range	Formula
C10:C14	C10	=VLOOKUP(A10,A$2:C$6,2+(B10="SI"),0)