Lookup Alternative

C.R.

Board Regular
Joined
Jul 1, 2002
Messages
76
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.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Dan Waterloo

Well-known Member
Joined
Jan 4, 2007
Messages
874
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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
 

C.R.

Board Regular
Joined
Jul 1, 2002
Messages
76
Thanks Guys ... both solutions worked nicely.
I learned something too.

C.R.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,093
Members
414,501
Latest member
mdhaumyu

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top