Remember Me?

 Straight Dope Message Board Remember Me?

#1
03-26-2008, 12:29 AM
 Koxinga Guest Join Date: Sep 1999 Location: Zeelandia Posts: 9,652
Excel lookup by row AND column

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

Last edited by Koxinga; 03-26-2008 at 12:30 AM.
#2
03-26-2008, 01:02 AM
 Inigo Montoya Guest Join Date: Mar 2004 Location: On the level, if inclined Posts: 13,635
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.

Last edited by Inigo Montoya; 03-26-2008 at 01:03 AM.
#3
03-26-2008, 01:23 AM
 Koxinga Guest Join Date: Sep 1999 Location: Zeelandia Posts: 9,652
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
#4
03-26-2008, 07:55 AM
 Inigo Montoya Guest Join Date: Mar 2004 Location: On the level, if inclined Posts: 13,635
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.
#5
03-26-2008, 08:35 AM
 Mullinator Guest Join Date: Sep 1999 Location: Georgia Posts: 4,158
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.

Mr. Excel
Excel User
#6
03-26-2008, 08:48 AM
 CookingWithGas Charter Member Join Date: Mar 1999 Location: Tysons Corner, VA, USA Posts: 11,664
Quote:
 Originally Posted by Inigo Montoya 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.
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:

1. 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).

2. The best source I have found (and I've see a bunch) for Excel and Excel VBA questions is at Ozgrid (free registration required).
#7
03-26-2008, 04:39 PM
 SCSimmons Guest Join Date: Mar 2001 Location: Arlington, TX Posts: 3,261
Quote:
 Originally Posted by CookingWithGas I have not analyzed Mullinator's answer but it looks good at first glance.
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.
__________________
-Christian
"You won't like me when I'm angry. Because I always back up my rage with facts and documented sources." -- The Credible Hulk
#8
03-26-2008, 08:50 PM
 Mullinator Guest Join Date: Sep 1999 Location: Georgia Posts: 4,158
Quote:
 Originally Posted by SCSimmons 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.
#9
03-26-2008, 08:55 PM
 Koxinga Guest Join Date: Sep 1999 Location: Zeelandia Posts: 9,652
Sounds great, I'll try it out--thanks everybody.

 Bookmarks

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is Off HTML code is Off Forum Rules
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Main     About This Message Board     Comments on Cecil's Columns/Staff Reports     General Questions     Great Debates     Elections     Cafe Society     The Game Room     Thread Games     In My Humble Opinion (IMHO)     Mundane Pointless Stuff I Must Share (MPSIMS)     Marketplace     The BBQ Pit

All times are GMT -5. The time now is 08:40 PM.

 -- Straight Dope v3.7.3 -- Sultantheme's Responsive vB3-blue Contact Us - Straight Dope Homepage - Archive - Top

Send questions for Cecil Adams to: cecil@straightdope.com