Vlookup: Please help! Example not the best
Posted by Todd on December 26, 2001 12:16 PM
1) Query Results
Apples Oranges Bananas
Color Red Oranges Yellow
Weight One Two Three
2) Query Results
Oranges Bananas
Weight Two Four
Okay, so I want a cell to give me the weight of bananas. The query I have run at diffrent times gives me the previous results. As you can see in 2), it does not give me the color and does not give me apples. This would throw off any Vlookup or Hlookup formula I had set for 1). I need a formula that will look at the table, and find the weight of bananas. The first time, it should bring back three. The second, it should bring back four. Could someone provide a formula for this specific example? I am somewhat of a newbie. Thanks.
Posted by Juan Pablo G. on December 26, 2001 12:21 PM
I still (Read all posts before this one) don't understand you r example. Can you do this. In a unused cell, put
=
and select your first table. Then, press F9. Copy what you see in the formula bar and paste it here. Repeat for table 2 please.
Juan Pablo G.
Posted by Todd on December 26, 2001 12:31 PM
{" ","Apples","Oranges","Bananas";"Color","Red","Orange","Yellow";"Weight","One","Two","Three"}
{" ","Oranges","Bananas";"Weight","Two","Four"}
Posted by Juan Pablo G. on December 26, 2001 12:44 PM
Ok, i think i understood what you want. Suppose your query start in A1. So, your first results would be in A1:D3, and second one would be in A1:C2.
I used this. In F1 i put
Bananas
in G1 i put
Weight
and in H1 i put this formula
=INDEX(A1:D3,MATCH(G1,A1:A3,0),MATCH(H1,A1:D1,0))
With this formula i got "Three" using the first table and "Four" using the second one.
Hope that's what you're looking for.
Juan Pablo G.
Posted by IML on December 26, 2001 12:45 PM
How about
=HLOOKUP("bananas",Sheet1!$A$1:$D$3,MATCH("weight",Sheet1!$A$2:$A$3,0)+1,0)
where your query will occupy at most A1:D3 on sheet one. You can expand this range if needed.
Posted by Todd on December 26, 2001 12:58 PM
Thanks!
=HLOOKUP("bananas",Sheet1!$A$1:$D$3,MATCH("weight",Sheet1!$A$2:$A$3,0)+1,0)
This is giving me what I want. Thank you for your help. I have been struggling with this for awhile. I will apply it to my real query and let you know.
Posted by IML on December 26, 2001 1:08 PM
Your welcome but,
I think Juan's is cleaner. Consider using
=INDEX(Sheet1!A1:D3,MATCH(G1,Sheet1!A1:A3,0),MATCH(H1,Sheet1!A1:D1,0))
to make the query more interactive. Or stagnent, it would be
=INDEX(Sheet1!A1:D3,MATCH("weight",Sheet1!A1:A3,0),MATCH("bananas",Sheet1!A1:D1,0))
