If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Nesting Lookup Functions
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!! |
#2
|
|||
|
|||
Nesting Lookup Functions
IF you are looking only for a response in the first row, why use a
VLOOKUP at all. Why not just a HLOOKUP? |
#3
|
|||
|
|||
Nesting Lookup Functions
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,MATC H(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. |
#4
|
|||
|
|||
Nesting Lookup Functions
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 |
#5
|
|||
|
|||
Nesting Lookup Functions
On Mar 23, 4:47*pm, tompl 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. |
Thread Tools | |
Display Modes | |
|
|