Lookup Alternative

[C.R.]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Week:	1	2	3	4	5	6	7	8	5	511
2		110	210	310	410	510	610	710	810		512
3		111	211	311	411	511	611	711	811		513
4		112	212	312	412	512	612	712	812		514
5		113	213	313	413	513	613	713	813		515
6		114	214	314	414	514	614	714	814		516
7		115	215	315	415	515	615	715	815		517
8		116	216	316	416	516	616	716	816		518
9		117	217	317	417	517	617	717	817		519
10		118	218	318	418	518	618	718	818		520
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.

 

[Dan Waterloo]

Using the Match and Index functions together should help.

 

[Dan Waterloo]

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Week:	1	2	3	4	5	6	7	8	5	511
2		110	210	310	410	510	610	710	810		512
3		111	211	311	411	511	611	711	811		513
4		112	212	312	412	512	612	712	812		514
5		113	213	313	413	513	613	713	813		515
6		114	214	314	414	514	614	714	814		516
7		115	215	315	415	515	615	715	815		517
8		116	216	316	416	516	616	716	816		518
9		117	217	317	417	517	617	717	817		519
10		118	218	318	418	518	618	718	818		520
11		119	219	319	419	519	619	719	819
12		120	220	320	420	520	620	720	820
Sheet2

 

[C.R.]

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

C.R.

 

[Yogi Anand]

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