Hi, I did a search but didn’t see excactly what I’m trying to do here:
I’d like to assign a cell to look up a value according to a row heading (akin to “VLOOKUP”) and also column heading (“HLOOKUP”). I’d like to do it in combination, though, and I don’t know how to use the two aforementioned functions in combination.
Any ideas? I’ll be glad to visit a previous thread if you can point me to where my search might have overlooked.
Thanks,
Koxinga
Are you talking about using the answer from a VLOOKUP to access info in another array using HLOOKUP?
You can do something odd like:
=HLOOKUP(VLOOKUP(a1,[array1],2),[array2],2)
which would use the value found in column 2 [array1] to reference info in row 2 of [array2]
or if you want to use one single array you can embed an “=IF” as in
=HLOOKUP(a1, [array1], if(a1>10,2,3))
which would use the info in column 2 or 3 depending on the value of a1.
Sorry, I may have been unclear.
I have a big table of stock prices with 20 company names as row headers and 200 dates as column headers. I want to answer the question “what was the share price of company X on such-and-such date?” So I’m looking up both variables separately.
Thanks again
Aassuming the date is in cell A1 and the company name is in A2:
=HLOOKUP(A1,[array],A2)
This will go to the date column A1 and return the value in the cell where the date and company A2 intersect.
Using a combo of Index and Match is actually a pretty good way to go when searching for something.
Example:
Let’s say your big table of info is on “Sheet1”. Your 20 company names are in cells A2:A21. In this case, I’ll be using the company names of 1, 2, 3…, 20 in those cells. Your 200 dates would be in cells B1:GS1 with the share prices within those parameters. For dates, I started with 1/1/2007 in cell B1 and incremented by 1 day in each following cell.
Now, let’s go to “Sheet2” where we will do your formula to look up the information. There are a couple of ways to do this. You can write the formula to look up something specific you mention in the formula or you can be a little more dynamic and have the formula look up something referenced in a cell. Since the dynamic way is a lot easier to re-use when you want to look up different values, we’ll go that way.
The formula that includes the specific items you want to look up:
In this example, I want to look up the value for Company 5 on 1/6/2007.
On Sheet2, cell A2, I would type in the number 5. In cell B1, I typed in 1/6/2007 (formatted to match how the dates are displayed on Sheet1.
Then, in cell B2, put in the following formula -
=(INDEX(Sheet1!$B$2:$P$21,(MATCH($B$1,Sheet1!$1:$1,FALSE)),(MATCH($A$2,Sheet1!$A:$A,FALSE))))
Clear as mud?
The index formula essentially is a search by row and column. Adding in the match lets you be a little more flexible in what you want to search for.
Breaking down the formula:
=(INDEX(Sheet1!$B$2:$P$21, is simply defining the array of data results you want to search and pull from
(MATCH($B$1,Sheet1!$1:$1,FALSE)), is where you set up the column portion of the array you want to match. You are saying look in cell B1. Take that value, go to the next sheet and find where that value is in row 1.
(MATCH($A$2,Sheet1!$A:$A,FALSE)))) is where you are saying look in cell A2. Find that value, go to the next sheet, look in column A. Then, the formula pulls whatever is at the intersection of your two matches and returns that value.
A couple of links that might help you more than my muddled explanation:
Mr. Excel
Excel User
Did you actually try this? It doesn’t work. The third parameter in HLOOKUP is a row index, which is to say an integer that tells which row within the range to look in. The function above returns #REF!.
I have not analyzed **Mullinator’s ** answer but it looks good at first glance.
Excel is not particularly friendly to this type of lookup. Because the description of the problem itself is so simple, you think the solution should be simple. But it requires more complexity than you would expect.
I recommend two things when asking Excel questions:
Do not describe down to the gnat’s eyeball level what you want, because you might often be trying to accomplish something in an awkward way to begin with. It’s better to describe your requirements at a high level (**Koxinga’s ** second post is perfect).
The best source I have found (and I’ve see a bunch) for Excel and Excel VBA questions is at Ozgrid (free registration required).
I popped into this thread to post the same response as Mullinator. It’s exactly right, I’ve used it many, many times, and it has the added advantage that when people look at your formula, they get all googly-eyed and their brains explode. 
Yup, I am more valuable at work because I don’t get freaked out by multiple parantheses and embedded formulas.
Sounds great, I’ll try it out–thanks everybody.