Prev: Relative Reference in Conditional Formattin
Next: join imported Excel 2007 files
From: Manish Seth Manish on 21 Jan 2010 10:31
Hi All,
I have a list as shown bellow, I want to pick up the date correcponding to
the value on cell A1 and the value in B2 or B3. So it is some thing like this
=Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example}
ColumnA ColumnB
ID Name
1 12082A IN-tuition
2 Ms. Gayle
3 Gayle Theresa
4 Sofiane
5 12082B tuition
6 Ms. Gayle
7 12298A Language Institute
8 Senouci Bereksi
9 AOUL
10 Bereksi


Kindly Help ASAP, As I am Stuck in my assignment.
From: Bob Bridges on 21 Jan 2010 18:28
Hi, Manish. The better way, of course, is to have the ID in col A, the
company name in B, the individual name in C and so forth; then you can use
VLOOKUP and specify the column you want depending on which datum you need to
pull for your ID. But if for some reason that option isn't open to you, then
what occurs to me is to use MATCH and INDIRECT instead of VLOOKUP. Like this:

1) In col A you have a list of IDs. In col B you have various data
associated with each ID, eg the company name in the same row as the ID and an
individual's name in the row following. I notice some of your sample data
have only those two fields, and others have more; the following method will
work with that, but you'll be able to look up only the first two fields,
nothing after that.

2) You have an ID you want to look up, say "12082B"

3) Your first formula, then, is =MATCH("12082B",A:A,0). MATCH returns the
row number in which 12082B was found, or an error if it isn't in the list.
In this case, that formula returns the value 5. Let's pretend that formula
is in I2.

4) But you don't want B5 ("tuition"), you want B6 ("Ms. Gayle"). So you use
the INDIRECT function, like this: =INDIRECT("B"&I2+1). I2 has the ID's now
number in it; this fomula adds one to that row number, tacks it on to B and
uses INDIRECT to look up B6.

If you need to have the data laid out as below and you want to look up one
of the values that is further down, such as "Bereksi" (which is 3 rows down
from its ID instead of 0 or 1), you're out of luck; unless you fill in the
missing rows for ALL your IDs, your formula will end up looking up the wrong
field in some cases.

--- "Manish Seth" wrote:
> I have a list as shown bellow, I want to pick up the date correcponding to
> the value on cell A1 and the value in B2 or B3. So it is some thing like this
> =Vlookup(a2,a:b[b2+1] or[b+2] {this is just a vierd example}
> ColumnA ColumnB
> ID Name
> 1 12082A IN-tuition
> 2 Ms. Gayle
> 3 Gayle Theresa
> 4 Sofiane
> 5 12082B tuition
> 6 Ms. Gayle
> 7 12298A Language Institute
> 8 Senouci Bereksi
> 9 AOUL
> 10 Bereksi
 | 
Pages: 1
Prev: Relative Reference in Conditional Formattin
Next: join imported Excel 2007 files