dynamic row and column lookup

[realinc]

I am working on a worksheet to lookup values from a table. I am having a problem figuring out the row and column dynamic lookup. I have two pieces of information. For example, 4 and $21,000. With these two pieces of information I need to be able to look across the top row to determine where 4 is, then look down that particular column and figure out the smallest value that is greater than or equal to $21,000. From this i need the percentage in the first column that this corresponds with. Using the table below the value I would be looking for would be 40%. I have tried a number of combinations using index, match, and offset but still have a problem writing a dynamic formula for determining the row (dollar value) after determining the correct column. This formula will need to be performed on multiple rows of data. Can anyone shed any light on what I might be missing

[TABLE="width: 821"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30%[/TD]
[TD] $ 13,900 [/TD]
[TD] $ 15,900 [/TD]
[TD] $ 17,900 [/TD]
[TD] $ 19,850 [/TD]
[TD] $ 21,450 [/TD]
[TD] $ 23,050 [/TD]
[TD] $ 24,650 [/TD]
[TD] $ 26,250 [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]40%[/TD]
[TD] $ 18,560 [/TD]
[TD] $ 21,200 [/TD]
[TD] $ 23,840 [/TD]
[TD] $ 26,480 [/TD]
[TD] $ 28,600 [/TD]
[TD] $ 30,720 [/TD]
[TD] $ 32,840 [/TD]
[TD] $ 34,960 [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50%[/TD]
[TD] $ 23,200 [/TD]
[TD] $ 26,500 [/TD]
[TD] $ 29,800 [/TD]
[TD] $ 33,100 [/TD]
[TD] $ 35,750 [/TD]
[TD] $ 38,400 [/TD]
[TD] $ 41,050 [/TD]
[TD] $ 43,700 [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]60%[/TD]
[TD] $ 27,840 [/TD]
[TD] $ 31,800 [/TD]
[TD] $ 35,760 [/TD]
[TD] $ 39,720 [/TD]
[TD] $ 42,900 [/TD]
[TD] $ 46,080 [/TD]
[TD] $ 49,260 [/TD]
[TD] $ 52,440 [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]80%[/TD]
[TD] $ 37,100 [/TD]
[TD] $ 42,400 [/TD]
[TD] $ 47,700 [/TD]
[TD] $ 52,950 [/TD]
[TD] $ 57,200 [/TD]
[TD] $ 61,450 [/TD]
[TD] $ 65,700 [/TD]
[TD] $ 69,900 [/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="8"></colgroup>[/TABLE]

The format of the table cant be changed as this will be used by others and the table will need to be updated periodically.

 

[sijpie]

Check the values you have in the table: are they numbers with $ formatting, or are they text with a $ in front?

Anyway if they are numbers the following will work. I have first broken down the formulas in steps with result in L6, then in L7 everything in one formula (not always the best)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1	2	3	4	5	6	7	8
2	30%	$13,900	$15,900	$17,900	$19,850	$21,450	$23,050	$24,650	$26,250			4	21000
3	40%	$18,560	$21,200	$23,840	$26,480	$28,600	$30,720	$32,840	$34,960			5
4	50%	$23,200	$26,500	$29,800	$33,100	$35,750	$38,400	$41,050	$43,700			E1:E10000
5	60%	$27,840	$31,800	$35,760	$39,720	$42,900	$46,080	$49,260	$52,440			3
6	80%	$37,100	$42,400	$47,700	$52,950	$57,200	$61,450	$65,700	$69,900			0.4
7												0.4
8
Sheet3

Cell Formulas
Range	Formula
L3	=MATCH(L2,A1:I1)
L4	=ADDRESS(1,L3,4,1)&":"&ADDRESS(10000,L3,4,1)
L5	=MATCH(M2,INDIRECT(L4),1)+1
L6	=INDEX(A:A,L5)
L7	=INDEX(A:A,MATCH(M2,INDIRECT(ADDRESS(1,MATCH(L2,A1:I1),4,1)&":"&ADDRESS(10000,MATCH(L2,A1:I1),4,1)),1)+1)

 

[realinc]

Thanks sijpie, the formula in L4 (address formula) is what i couldnt get past. I had to tweak the end product to work between two worksheets but it worked perfectly. I had to place a "Sheet2!"& in front of Address in L4 to reference my chart values from another worksheet. Not sure if that was the most efficient but worked for my use. I also used an "if" to compare the date to two different charts. Im sure there is a cleaner way to work out this formula and Ill keep working at it. Thanks again.
End formula :
=IF(Q9>=Sheet2!$A$1,IF(ISERROR(INDEX(Sheet2!$A$2:$A$6,MATCH('RCB TEST RESULTS'!H9,INDIRECT("Sheet2!"&ADDRESS(1,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$1:$I$1),4,1)&":"&ADDRESS(6,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$1:$I$1),4,1)),1))),"OI",INDEX(Sheet2!$A$2:$A$6,MATCH('RCB TEST RESULTS'!H9,INDIRECT("Sheet2!"&ADDRESS(1,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$1:$I$1),4,1)&":"&ADDRESS(6,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$1:$I$1),4,1)),1))),IF(ISERROR(INDEX(Sheet2!$A$9:$A$13,MATCH('RCB TEST RESULTS'!H9,INDIRECT("Sheet2!"&ADDRESS(8,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$8:$I$8),4,1)&":"&ADDRESS(13,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$8:$I$8),4,1)),1))),"OI",INDEX(Sheet2!$A$9:$A$13,MATCH('RCB TEST RESULTS'!H9,INDIRECT("Sheet2!"&ADDRESS(8,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$8:$I$8),4,1)&":"&ADDRESS(13,MATCH('RCB TEST RESULTS'!E9,Sheet2!$A$8:$I$8),4,1)),1))))

 

[Aladin Akyurek]

Try...

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[TD="width: 64, align: right"]4[/TD]
[TD="width: 64, align: right"]5[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64, align: right"]7[/TD]
[TD="width: 64, align: right"]8[/TD]
[TD="width: 64"][/TD]
[TD="width: 87"][/TD]
[TD="width: 71"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]30%[/TD]
[TD="width: 64, align: right"]$13,900[/TD]
[TD="width: 64, align: right"]$15,900[/TD]
[TD="width: 64, align: right"]$17,900[/TD]
[TD="width: 64, align: right"]$19,850[/TD]
[TD="width: 64, align: right"]$21,450[/TD]
[TD="width: 64, align: right"]$23,050[/TD]
[TD="width: 64, align: right"]$24,650[/TD]
[TD="width: 64, align: right"]$26,250[/TD]
[TD][/TD]
[TD="width: 87, align: right"]4[/TD]
[TD="width: 71, align: right"]$21,000[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]40%[/TD]
[TD="width: 64, align: right"]$18,560 [/TD]
[TD="width: 64, align: right"]$21,200[/TD]
[TD="width: 64, align: right"]$23,840[/TD]
[TD="width: 64, align: right"]$26,480[/TD]
[TD="width: 64, align: right"]$28,600[/TD]
[TD="width: 64, align: right"]$30,720[/TD]
[TD="width: 64, align: right"]$32,840[/TD]
[TD="width: 64, align: right"]$34,960[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]50%[/TD]
[TD="width: 64, align: right"]$23,200
[/TD]
[TD="width: 64, align: right"]$26,500[/TD]
[TD="width: 64, align: right"]$29,800[/TD]
[TD="width: 64, align: right"]$33,100[/TD]
[TD="width: 64, align: right"]$35,750[/TD]
[TD="width: 64, align: right"]$38,400[/TD]
[TD="width: 64, align: right"]$41,050[/TD]
[TD="width: 64, align: right"]$43,700[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]60%[/TD]
[TD="width: 64, align: right"]$27,840[/TD]
[TD="width: 64, align: right"]$31,800[/TD]
[TD="width: 64, align: right"]$35,760[/TD]
[TD="width: 64, align: right"]$39,720[/TD]
[TD="width: 64, align: right"]$42,900[/TD]
[TD="width: 64, align: right"]$46,080[/TD]
[TD="width: 64, align: right"]$49,260[/TD]
[TD="width: 64, align: right"]$52,440[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 64, align: right"]80%[/TD]
[TD="width: 64, align: right"]$37,100[/TD]
[TD="width: 64, align: right"]$42,400[/TD]
[TD="width: 64, align: right"]$47,700[/TD]
[TD="width: 64, align: right"]$52,950[/TD]
[TD="width: 64, align: right"]$57,200[/TD]
[TD="width: 64, align: right"]$61,450[/TD]
[TD="width: 64, align: right"]$65,700[/TD]
[TD="width: 64, align: right"]$69,900[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

M2, just enter:

=INDEX($A$2:$A$6,MATCH(L2,INDEX($B$2:$I$6,0,MATCH(K2,$B$1:$I$1,0)),1)+
  (LOOKUP(L2,INDEX($B$2:$I$6,0,MATCH(K2,$B$1:$I$1,0))) <  L2))

 

[Jerry Sullivan]

Hi Aladin, In puzzling with this yesterday I found your example here of a technique to do a "smallest value greater than or equal to" lookup on an ascending sorted list.
http://www.mrexcel.com/forum/excel-questions/275989-match-formula-match-type-1-not-working.html
I ran out of time before I was able to adapt this as you have (I'm glad to see I was on a good path).

In studing that simpler example, it appears this technique doesn't correctly handle the case of the lookup value being less than the first item.
In that thread, a value of 0.5 in B1 should return a result of 1 (the first value in the list), but due to the nature of a match_type argument of 1, it returns #N/A.

One fix for that would be...
=IF(B1< B3,B3,INDEX($B$3:$B$12, MATCH(B1,$B$3:$B$12,1)+(LOOKUP(B1,$B$3:$B$12)< B1) ))

I believe the formula you suggested for this thread needs a similar refinement. For example, if the lookup value is $5,000, the "smallest value that is greater than or equal to $5,000" for any column in this table will be that column's first value (not #N/A).

 

[Aladin Akyurek]

Hi Aladin, In puzzling with this yesterday I found your example here of a technique to do a "smallest value greater than or equal to" lookup on an ascending sorted list.
http://www.mrexcel.com/forum/excel-questions/275989-match-formula-match-type-1-not-working.html
I ran out of time before I was able to adapt this as you have (I'm glad to see I was on a good path).

In studing that simpler example, it appears this technique doesn't correctly handle the case of the lookup value being less than the first item.
In that thread, a value of 0.5 in B1 should return a result of 1 (the first value in the list), but due to the nature of a match_type argument of 1, it returns #N/A.

One fix for that would be...
=IF(B1< B3,B3,INDEX($B$3:$B$12, MATCH(B1,$B$3:$B$12,1)+(LOOKUP(B1,$B$3:$B$12)< B1) ))

I believe the formula you suggested for this thread needs a similar refinement. For example, if the lookup value is $5,000, the "smallest value that is greater than or equal to $5,000" for any column in this table will be that column's first value (not #N/A).

Yep. We have this issue when the table is "incomplete" as this feature shows up with LookUpValue < Minimum(MatchRange). It's not always clear what would be appropriate:

1) #N/A

2) Test LookUpValue < MIN(MatchRange) --> Some option

3) IFERROR(Expression, Some option)

4) Modify MatchRange: Like starting it with 0 or -9.99999999999999E+307 with appropriate correlates