dynamic row and column lookup

realinc · Mar 19, 2014

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

	A	B	C	D	E	F	G	H	I
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
3	40%	$ 18,560	$ 21,200	$ 23,840	$ 26,480	$ 28,600	$ 30,720	$ 32,840	$ 34,960
4	50%	$ 23,200	$ 26,500	$ 29,800	$ 33,100	$ 35,750	$ 38,400	$ 41,050	$ 43,700
5	60%	$ 27,840	$ 31,800	$ 35,760	$ 39,720	$ 42,900	$ 46,080	$ 49,260	$ 52,440
6	80%	$ 37,100	$ 42,400	$ 47,700	$ 52,950	$ 57,200	$ 61,450	$ 65,700	$ 69,900

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 · Mar 20, 2014

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

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 · Mar 20, 2014

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 · Mar 20, 2014

Try...

	1	2	3	4	5	6	7	8
30%	$13,900	$15,900	$17,900	$19,850	$21,450	$23,050	$24,650	$26,250	4	$21,000	0.4
40%	$18,560	$21,200	$23,840	$26,480	$28,600	$30,720	$32,840	$34,960
50%	$23,200	$26,500	$29,800	$33,100	$35,750	$38,400	$41,050	$43,700
60%	$27,840	$31,800	$35,760	$39,720	$42,900	$46,080	$49,260	$52,440
80%	$37,100	$42,400	$47,700	$52,950	$57,200	$61,450	$65,700	$69,900

M2, just enter:

Rich (BB code):

=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 · Mar 20, 2014

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 · Mar 20, 2014

Jerry Sullivan said:
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

dynamic row and column lookup

realinc

New Member

Excel Facts

sijpie

Well-known Member

realinc

New Member

Aladin Akyurek

MrExcel MVP

Jerry Sullivan

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

dynamic row and column lookup

realinc

New Member

Excel Facts

sijpie

Well-known Member

realinc

New Member

Aladin Akyurek

MrExcel MVP

Jerry Sullivan

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock