URGENT! Help Needed with Lookup Formula

  • Thread starter Thread starter Lucas Bravo
  • Start date Start date
L

Lucas Bravo

I have a table such as the following:

STORE ASSISTANT MANAGER
Store A John Bob
Store B John Bob
Store C Mary Terry

Let's assume that this range is called Data.

I want to use one single formula to lookup and return different values in
the table. This means that the same formula should be able to accomplish the
following:

a) Lookup any store's Assistant
b) Lookup any store's Manager
c) Lookup any Assistant's Manager

The only thing that I want to be able to change is the column headings that
I am using as lookup value and as return value.

One approach could be to define several Range Names, like: DataByStore (Cols
A thru C) and DataByAssistant (Cols B thru C). However, I want to avoid this
because I have over 20 columns that I would be looking by (this example has
been over-simplified)

Any ideas of how to make this work with only one formula and one range name?

Thanks!

Lucas
 
Yes, but won't work in this case. I need this lookup to be dynamic, without
requiring any type of user interaction.
 
Hi Lucas
a solution for your specification a) and b) could be:
=INDEX(A2:C4,MATCH(A7,A2:A4,0),MATCH(A8,A1:C1,0))
where A7 stores your store name and A8 stores your result column
(Assistant or Manager or Store)
Note: this will always return the first instance for your search
criteria (not multiple results)

A solution for a), b) and c) could be the following
=INDEX(A2:C4,MATCH(A7,OFFSET($A$2,0,MATCH(A9,A1:C1,0)-1,3),0),MATCH(A8,
A1:C1,0))
there in addition to above A9 stores the search column

HTH
Frank
 
Highlight the entire sheet you're referencing and name it
data.

Use the vlookup function. You can reference the first
column in data for example store#.

The vlookup will find the store# and return the value in
the column you specify.

Ex. =vlookup(store#,data,2,false) will return the value in
column 2 when it finds the store#.
 
Lucas -
It appears that this issue was resolved in another post:

If this issue has not been resolved - please let me know.
Thanks,
Jon Barchenger
--------------------
**From: "Lucas Bravo" <[email protected]>
**References: <uz#[email protected]> <[email protected]>
**Subject: Re: URGENT! Help Needed with Lookup Formula
**Date: Mon, 2 Feb 2004 16:08:52 -0500
**Lines: 75
**X-Priority: 3
**X-MSMail-Priority: Normal
**X-Newsreader: Microsoft Outlook Express 5.50.4920.2300
**X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4920.2300
**Message-ID: <[email protected]>
**Newsgroups: microsoft.public.excel.worksheet.functions
**NNTP-Posting-Host: 65.174.40.114
**Path: cpmsftngxa07.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
**Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.worksheet.functions:186988
**X-Tomcat-NG: microsoft.public.excel.worksheet.functions
**
**Yes, but won't work in this case. I need this lookup to be dynamic, without
**requiring any type of user interaction.
**
****> Not a formula I know, but have you considered just using Autofilter on
**that kind
**> of data?
**>
**> http://www.contextures.com/xlautofilter01.html
**>
**> --
**> Regards
**> Ken....................... Microsoft MVP - Excel
**> Sys Spec - Win XP Pro / XL 00/02/03
**>
**> --------------------------------------------------------------------------
**--
**> It's easier to beg forgiveness than ask permission :-)
**> --------------------------------------------------------------------------
**--
**>
**>
**>
**> **> > I have a table such as the following:
**> >
**> > STORE ASSISTANT MANAGER
**> > Store A John Bob
**> > Store B John Bob
**> > Store C Mary Terry
**> >
**> > Let's assume that this range is called Data.
**> >
**> > I want to use one single formula to lookup and return different values
**in
**> > the table. This means that the same formula should be able to accomplish
**the
**> > following:
**> >
**> > a) Lookup any store's Assistant
**> > b) Lookup any store's Manager
**> > c) Lookup any Assistant's Manager
**> >
**> > The only thing that I want to be able to change is the column headings
**that
**> > I am using as lookup value and as return value.
**> >
**> > One approach could be to define several Range Names, like: DataByStore
**(Cols
**> > A thru C) and DataByAssistant (Cols B thru C). However, I want to avoid
**this
**> > because I have over 20 columns that I would be looking by (this example
**has
**> > been over-simplified)
**> >
**> > Any ideas of how to make this work with only one formula and one range
**name?
**> >
**> > Thanks!
**> >
**> > Lucas
**> >
**> >
**>
**>
**> ---
**> Outgoing mail is certified Virus Free.
**> Checked by AVG anti-virus system (http://www.grisoft.com).
**> Version: 6.0.576 / Virus Database: 365 - Release Date: 30/01/2004
**>
**>
**
**
**
 
Back
Top