# Lookup Alternative

#### C.R.

##### Board Regular
Hello All,

I have this list of accounts to call on covering an 8 week call cycle. What I am trying to do is type a week # into cell J1 and have the accounts for that week post in column K. The cumbersome formula below (based on an Aladin solution in another post) works, but I’ve hit the maximum 7 nested IF’s and I have 8 weeks to consider.
NewMaster.xls
ABCDEFGHIJK
1Week:123456785511
2110210310410510610710810512
3111211311411511611711811513
4112212312412512612712812514
5113213313413513613713813515
6114214314414514614714814516
7115215315415515615715815517
8116216316416516616716816518
9117217317417517617717817519
10118218318418518618718818520
WeekSeq

=IF(\$J\$1=1,LOOKUP(9.99999999999999E+307,\$B\$2:B2)+1,
IF(\$J\$1=2,LOOKUP(9.9999999999999E+307,\$C\$2:C2)+1,
IF(\$J\$1=3,LOOKUP(9.9999999999999E+307,\$D\$2:D2)+1,
IF(\$J\$1=4,LOOKUP(9.9999999999999E+307,\$E\$2:E2)+1,
IF(\$J\$1=5,LOOKUP(9.9999999999999E+307,\$F\$2:F2)+1,
IF(\$J\$1=6,LOOKUP(9.9999999999999E+307,\$G\$2:G2)+1,
IF(\$J\$1=7,LOOKUP(9.9999999999999E+307,\$H\$2:H2)+1)))))))

Any ideas to clean this up? Thanks in advance.

C.R.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Using the Match and Index functions together should help.

E.g., in K2 you would put this formula
=INDEX(B2:I2, 1, MATCH(J\$1, B\$1:I\$1))

Then you could copy that formula down the K column.

Wouldn't that be an HLOOKUP - which by definition is exactly the same as Vlookup, just Transposed...

in K2

=HLOOKUP(J\$1,B:I,Row()+2,FALSE)
filled down
personal.xls
ABCDEFGHIJK
1Week:123456785511
2110210310410510610710810512
3111211311411511611711811513
4112212312412512612712812514
5113213313413513613713813515
6114214314414514614714814516
7115215315415515615715815517
8116216316416516616716816518
9117217317417517617717817519
10118218318418518618718818520
11119219319419519619719819
12120220320420520620720820
Sheet2

Thanks Guys ... both solutions worked nicely.
I learned something too.

C.R.

Hi C.R.

For your data layout, in addition to contributions from Dan Waterloo and jonmo1, to get the results:

510,511,512,513,514,515,516,517,518

you may also try ...

array formula in cells K1:K9 ... =INDEX(B:F,ROW(2:10),J1)

or

normally entered formula in cell K1 ... =INDEX(B:F,ROWS(\$1:2),J\$1)

then copy it to K1:K9

Replies
17
Views
364
Replies
9
Views
231
Replies
7
Views
977
Replies
2
Views
129
Replies
6
Views
766

1,221,498
Messages
6,160,161
Members
451,627
Latest member
WORBY10

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back