Vlookup

Posted by Todd Stockard on December 26, 2001 10:58 AM

Basically, in sheet2, I want to return what ends up when "E" and "5" line up in row 1, column A. sheet1 contains a query which one time produces the first results and the next, it produces the second results. Vlookup and Hlookup do not seem to work because E and 5 line up in different rows AND columns.

Is there a way to have sheet2, row 1, column A, always search the columns for "E" and the rows for "5" and give me the results, which in this case is apples

A B C D E

1
2
3
4
5 Apples

A C D E
1
2
4
5 Apples

Posted by Todd Stockard on December 26, 2001 11:01 AM

A B C D E 2 3 4 5_________Apples 1 2 4 5_______Apples

Posted by Jacob on December 26, 2001 11:16 AM

This might work

At the end of the columns of data put the row numbers and at the end of the rows put the column number: these can be hidden.

A B C D E

1 1
2 2
3 3 &LT;== Column Numbers
4 4
5_________Apples 5

1 2 3 4 5 &LT;== Column Numbers

Then Try This: In a cell lets say "A1" put = Vlookup("E","YourRange","Column numbers are in in this case 6)
in "B1" do Hlookup the same way

in "C1" put = B1 & C1

This will give the address of the cell intersected by your values E and 5.

Then in another cell put =indirect(C1)

Hope this gets you started

Jacob

Posted by IML on December 26, 2001 11:36 AM

A B C D E 2 3 4 5 Apples 1 2 4 5 Apples

I'm not sure I exactly follow, but lets say your table is on sheet 1 A1:F6. Cell A1 is blank.

You could use
=INDIRECT("Sheet1!"&ADDRESS(MATCH(5,Sheet1!A2:A6,0)+1,MATCH("a",Sheet1!B1:F1,0)+1))

to match 5 and A. Or these constants could be replaced by a cell address.

Good luck

Posted by Todd on December 26, 2001 11:56 AM

Apples Oranges Bananas
Color Red Orange Yellow
Weight One Two Three

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, it does not give me the color the second time and does not give me apples. This would throw off any Vlookup or Hlookup formula. 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 IML on December 26, 2001 12:03 PM

Sorry, I'm completely lost. good luck (nt)

Apples Oranges Bananas Color Red Orange Yellow Weight One Two Three Weight Two Four