The Straight Dope

Go Back   Straight Dope Message Board > Main > General Questions

Reply
 
Thread Tools Display Modes
  #1  
Old 03-26-2008, 12:29 AM
Koxinga Koxinga is offline
Guest
 
Join Date: Sep 1999
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..
Reply With Quote
Advertisements  
  #2  
Old 03-26-2008, 01:02 AM
The Great Sun Jester The Great Sun Jester is offline
Guest
 
Join Date: Mar 2004
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 The Great Sun Jester; 03-26-2008 at 01:03 AM..
Reply With Quote
  #3  
Old 03-26-2008, 01:23 AM
Koxinga Koxinga is offline
Guest
 
Join Date: Sep 1999
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
Reply With Quote
  #4  
Old 03-26-2008, 07:55 AM
The Great Sun Jester The Great Sun Jester is offline
Guest
 
Join Date: Mar 2004
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.
Reply With Quote
  #5  
Old 03-26-2008, 08:35 AM
Mullinator Mullinator is offline
Guest
 
Join Date: Sep 1999
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
Reply With Quote
  #6  
Old 03-26-2008, 08:48 AM
CookingWithGas CookingWithGas is offline
Charter Member
 
Join Date: Mar 1999
Location: Tysons Corner, VA, USA
Posts: 9,780
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).
Reply With Quote
  #7  
Old 03-26-2008, 04:39 PM
SCSimmons SCSimmons is offline
Guest
 
Join Date: Mar 2001
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
Reply With Quote
  #8  
Old 03-26-2008, 08:50 PM
Mullinator Mullinator is offline
Guest
 
Join Date: Sep 1999
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.
Reply With Quote
  #9  
Old 03-26-2008, 08:55 PM
Koxinga Koxinga is offline
Guest
 
Join Date: Sep 1999
Sounds great, I'll try it out--thanks everybody.
Reply With Quote
Reply



Bookmarks

Thread Tools
Display Modes

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 Jump


All times are GMT -5. The time now is 02:46 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.

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

Send comments about this website to: webmaster@straightdope.com

Terms of Use / Privacy Policy

Advertise on the Straight Dope!
(Your direct line to thousands of the smartest, hippest people on the planet, plus a few total dipsticks.)

Publishers - interested in subscribing to the Straight Dope?
Write to: sdsubscriptions@chicagoreader.com.

Copyright 2013 Sun-Times Media, LLC.