Lookup/Match Question

[jmattingly85]

Hi. I am trying to see if Excel has function to match/return a result.

I've posted my data below. I'd like to look up the number in Column A (Size). Then, it would go to that row Column E. Then, it would look at the amount in Column B, and determine if the amount in column B is less than the amounts in Columns F, G, H, or I, and then return the result in Column C that matches the headers in F2, G2, H2, I2.

For example, in Row 1. The amount size is 4 and the amount is $35,557. It would first look at E6 (size of 4), and then return 50% in Column C (since $35,557 is less than $36,000). Is this possible?

Row/Columns	A	B	C	D	E	F	G	H	I​
1	Size	Income	Determination
2	4	$35,557​			Size	30%	50%	80%	120%
3	5	$36,272​			1	$ 15,120	$ 25,200	$ 40,320	$ 60,480
4	4	$34,540​			2	$ 17,280	$ 28,800	$ 46,080	$ 69,120
5	1	$2,916​			3	$ 19,440	$ 32,400	$ 51,840	$ 77,760
6	2	$15,761​			4	$ 21,600	$ 36,000	$ 57,600	$ 86,400
7	4	$28,251​			5	$ 23,340	$ 38,900	$ 62,240	$ 93,360
8	4	$42,172​			6	$ 25,080	$ 41,800	$ 66,880	$ 100,320

 

[Fluff]

What version of Excel are you using?

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’)

 

[jmattingly85]

What version of Excel are you using?

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’)

Thank you - I've updated that information.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Size	Income	Determination
2	4	35,557	50%		Size	30%	50%	80%	120%
3	5	36,272	50%		1	15,120	25,200	40,320	60,480
4	4	34,540	50%		2	17,280	28,800	46,080	69,120
5	1	2,916	30%		3	19,440	32,400	51,840	77,760
6	2	15,761	30%		4	21,600	36,000	57,600	86,400
7	4	28,251	50%		5	23,340	38,900	62,240	93,360
8	4	42,172	80%		6	25,080	41,800	66,880	100,320
Master

Cell Formulas
Range	Formula
C2:C8	C2	=INDEX($F$2:$I$2,XMATCH(B2,INDEX($F$3:$I$8,MATCH(A2,$E$3:$E$8,0),0),1))

 

[jmattingly85]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Size	Income	Determination
2	4	35,557	50%		Size	30%	50%	80%	120%
3	5	36,272	50%		1	15,120	25,200	40,320	60,480
4	4	34,540	50%		2	17,280	28,800	46,080	69,120
5	1	2,916	30%		3	19,440	32,400	51,840	77,760
6	2	15,761	30%		4	21,600	36,000	57,600	86,400
7	4	28,251	50%		5	23,340	38,900	62,240	93,360
8	4	42,172	80%		6	25,080	41,800	66,880	100,320
Master

Cell Formulas
Range	Formula
C2:C8	C2	=INDEX($F$2:$I$2,XMATCH(B2,INDEX($F$3:$I$8,MATCH(A2,$E$3:$E$8,0),0),1))

That did it. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.