Prev: Data Validation List from a Dynamic Named Range on Another Workshe
Next: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!!
From: eionjess on 23 Mar 2010 16:12
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!!


From: Ziggy on 23 Mar 2010 16:51
IF you are looking only for a response in the first row, why use a
VLOOKUP at all. Why not just a HLOOKUP?

From: Ziggy on 23 Mar 2010 18:36


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.
From: tompl on 23 Mar 2010 18:47
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
From: Ziggy on 25 Mar 2010 13:29
On Mar 23, 4:47 pm, tompl <to...(a)discussions.microsoft.com> wrote:
> 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.
 | 
Pages: 1
Prev: Data Validation List from a Dynamic Named Range on Another Workshe
Next: "AND FUNCTION" RETURNS FALSE WHEN CRITERIA IS TRUELY MET. HELP!!!