URGENT! Help Needed with Lookup Formula

[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

 

[Ken Wright]

Not a formula I know, but have you considered just using Autofilter on that kind
of data?

http://www.contextures.com/xlautofilter01.html

 

[Lucas Bravo]

Yes, but won't work in this case. I need this lookup to be dynamic, without
requiring any type of user interaction.

 

[Frank Kabel]

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

 

[Bill]

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 Bravo]

Frank,

That is exactly what I needed. Thanks a lot!

Lucas

 

[Jon Barchenger[MS]]

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
**>
**>
**
**
**