[mso] Re: Excel Lookup Function, corrected
- From: Wilson Baptista Junior <wilsonbaptista@xxxxxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Sun, 18 Jan 2009 18:14:02 -0200
Hi Adam,
the formula follows (from the inside out) the order of your message:
VLOOKUP(G2,$C$5:$C$9,1) finds in the side header (cells C5 to C9) the cell
containing the variable value (which is stored in cell G2).
Then the two MATCH functions
OFFSET($D$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0) counts the number of
lines down from the top header cell D4 that the line containing the variable
value is,
OFFSET($G$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0) does the same for the
top header cell G4,
so those two values define the lower boundary of an array which contains the
header line and the line the variable value is in.
And the final LOOKUP function
=LOOKUP(H2,OFFSET($D$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0):OFFSET($G$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0),$D$4:$G$4)
scans the lower line of this array (the line the variable value is on) and
finds in the top line (which is your top header) the cell corresponding to the
constante value (stored in cell H2). Note that this constant can be a variable,
as long as it's stored in cell H2.
I'm sending, in another email, to your private email, the spreadsheet, which
will make things clearer.
Wilson
At 11:47 18/1/2009 -0800,Adam Peszel wrote:
>Wilson
>Looking over your command, and I want be able to replicate at a later time.
>Which Lookup did you start with and which lines do they commands go with?
>I don't want to make a mistake with this. It's better to play dumb and be
>taught then
>make an assumption.
>Adam
>--- On Sat, 1/17/09, Wilson Baptista Junior <wilsonbaptista@xxxxxxxxxxxx>
>wrote:
>
>From: Wilson Baptista Junior <wilsonbaptista@xxxxxxxxxxxx>
>Subject: [mso] Re: Excel Lookup Function, corrected
>To: mso@xxxxxxxxxxxxx
>Date: Saturday, January 17, 2009, 9:37 AM
>
>Hi, there was a typing error in my previous message, the table spans cells C4
>to
>G9, and not A4 to A9, and the side header runs from cell C5 to cell C9.
>The formula is correct.
>Sorry <g>
>Wilson
>
>Yes, this can be done in Excel with one formula, although the formula will be a
>somewhat lenghty one.
>Lets assume that your table spans cells C4 to G9, with the side header running
>from cell C5 to cell C9, and the top header running from cell D4 to G4.
>Suppose that you have, in cell G2, the value of the variable you're going
>to search for in the side header, and in cell H2 the constant you want to
>search
>for.
>The formula will be:
>
>=LOOKUP(H2,OFFSET($D$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0):OFFSET($G$4,MATCH(VLOOKUP(G2,$C$5:$C$9,1),$C$5:$C$9,0),0),$D$4:$G$4)
>
>(beware of line wrap on the message)
>
>Probably Linda or Diane will come up with a shorter and easier way to do this,
>but this one works.
>If you want to, I can send you, to your email address, the worksheet I used to
>make up this example, as this list doesn't accept attachments.
>Best,
>Wilson
>
> Adam Peszel wrote:
>>Hello,
>>I am trying to run a lookup in Excel but do not know if this can be done.
>>I have a populated table that cannot/should not be modified with a side
>header of values,
>>and a top header with values. I need to find a row for a variable using the
>side header. Once
>>there, I need to find a cell with a value equal to or next value greater
>than the a constant. I
>>then need to return the value in the column header that corresponds. Can
>this be done?
>>
>>One of my brothers said it is a reverse lookup and the only way he knows
>this can be done
>>is with Access. If this is the case, can I use OpenOffice Base to program
>at home and
>>open it on Access at work? I know this is not a MS product, but I do not
>have Access in my version of Office. Plus, I have no clue how to use either.
>>
>>Thank you in advance.
>>Adam
--------------------------------------------------------------
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to share files with the group, you must join our Yahoo sister group.
This group will not allow for posting of emails, but will allow you to join
and share problem files, templates, etc.:
http://tech.groups.yahoo.com/group/MicrosoftOffice . This group is for FILE
SHARING ONLY.
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
