Nesting Lookup Functions

[eionjess]

Is it possible to nest an HLOOKUP in a VLOOKUP equation?

Here's what I have: =VLOOKUP(C27,A2:B5,(HLOOKUP(D27+1,INDIRECT(A2),21)

Each VLOOKUP lookup value is the start of a range that I would like to be
the first row of an HLOOKUP range. I might be going about this the wrong
way, so if you have suggestions, I'm definitely open to it!!

The objective is to provide Excel with two associated values (i.e.,
department and production value in a given range) and have Excel produce the
production value ranking. I have a ton of departments each with a range of
production values but only one specified ranking (1 - 20).

Thanks in advance!!

 

[Ziggy]

IF you are looking only for a response in the first row, why use a
VLOOKUP at all. Why not just a HLOOKUP?

 

[Ziggy]

1 Jan Feb Mar Apr May
2 110 100 200 400 800 1600
3 120 200 400 800 1600 3200
4 130 400 800 1600 3200 6400
5 340 800 1600 3200 6400 12800
6 345 1600 3200 6400 12800 25600
7 350 3200 6400 12800 25600 51200
8 360 6400 12800 25600 51200 102400




Feb

130 800

~=HLOOKUP($L$87,$M$75:$R$82,INDEX($L$76:$L$82,MATCH(L89,M76:M82,0))
+1,FALSE)

I have gotten to like the combo of Match/Index better than the
lookups. try this for the combo. The 1,2,3 were column L starting at
row 75 for the formula.

 

[tompl]

Try the index function combined with two match functions. One match gives
you the row and the other gives you the column.

e.g. =INDEX(A1:E5,MATCH(H1,A1:A5,0),MATCH(I1,A1:E1,0))

Tom

 

[Ziggy]

Try the index function combined with two match functions.  One match gives
you the row and the other gives you the column.

e.g. =INDEX(A1:E5,MATCH(H1,A1:A5,0),MATCH(I1,A1:E1,0))

Tom

Very nice.

I could only get this to work IF the column match came first.