Vlookup multiple values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

suraneniaditya - 04 Mar 2008 13:08 GMT

Dear all

I have a sheet which looks as follows

Sheet 1

COl A l COl B l COl C l COl D
Name l Tax l Tax Recpt No l Date of
deduction

John l 22 l 3 l
21.12.08
Raj l 36 l 8 l
03.01.08
Sam l 35 l 6 l
01.01.08
Vas l 89 l 7 l
16.05.08
John l 92 l 5 l
12.04.08
Raj l112 l 9 l
31.06.08
Jane l121 l 12 l
13.08.08
Mader l 92 l 13 l
23.09.08
Varun l181 l 15 l
26.11.08
Jane l145 l 19 l
24.07.08
Raj l213 l 26 l
29.03.08

In Sheet 2 I need to get all the values in the three fields against
names which lookl ike this

COl B COl C COl D
Name Tax Tax Recpt No Date of deduction

Jane
Raj
John
Vas
Jane
Raj
Varun
Raj
Mader
John
Sam

Pls help me

Adi

Signature

suraneniaditya

Reply to this Message

Max - 04 Mar 2008 15:19 GMT

In Sheet2,
Put in B2:
=IF(ISNA(MATCH($A2,Sheet1!$A:$A,0)),"",INDEX(Sheet1!B:B,MATCH($A2,Sheet1!$A:$A,0)))
Copy B2 to D2, fill down as far as required

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> Dear all
[quoted text clipped - 51 lines]
>
> Adi

Reply to this Message

suraneniaditya - 06 Mar 2008 12:17 GMT

Thank You very much for ur interest MAX.

The formula gave me a blank result. Would u be interested if i can send
u the data sheet. If yes how shall i Do it

Adi

Max;634415 Wrote:

> In Sheet2,
> Put in B2:
[quoted text clipped - 57 lines]
> >
> > Adi

Signature

suraneniaditya

Reply to this Message

Max - 06 Mar 2008 23:27 GMT

> The formula gave me a blank result.

If there should be an "obvious" match from you looking at it over there, but
the earlier formula doesn't seem to return the expected results, it's
usually a case of data consistency between the lookup values and those in
the reference col (the looked-up col)

You could try one of these 3 variations of the earlier in B2
to improve robustness in matching:

a. Lookup values in A2 down are text strings

Remove any extra white spaces which may be present in the lookup values:
=IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"",
INDEX(Sheet1!B:B,MATCH(TRIM($A2),Sheet1!$A:$A,0)))

b. Lookup values in A2 down are input numbers (ie real numbers) while those
in the reference col are text numbers

Convert the lookup values to be text numbers instead, using either:

=IF(ISNA(MATCH($A2&"",Sheet1!$A:$A,0)),"",
INDEX(Sheet1!B:B,MATCH($A2&"",Sheet1!$A:$A,0)))

or something like this, if there are leading zeros:

=IF(ISNA(MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)),"",
INDEX(Sheet1!B:B,MATCH(TEXT($A2,"000000"),Sheet1!$A:$A,0)))

Try the above 1st, see how it goes ..

And if you really need to, you could always
upload a small sample* file & post a direct link to it here
*desensitize it appropriately

You could use:
http://www.freefilehosting.net/

**Keeping discussions visible here is to the benefit of all**

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>
> Thank You very much for ur interest MAX.
[quoted text clipped - 3 lines]
>
> Adi

Reply to this Message

Max - 07 Mar 2008 03:58 GMT

Additional clarifications for this part:

> a. Lookup values in A2 down are text strings
> Remove any extra white spaces which may be present in the lookup values:
> =IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"",
> INDEX(Sheet1!B:B,MATCH(TRIM($A2),Sheet1!$A:$A,0)))

If the problem with extra white spaces is happening with the text in the
reference col as well, ie: Sheet1!$A:$A, the simplest way is, in Sheet1, to
use a helper col to TRIM and overwrite col A. Eg you could place in say, E2,
copied down: =TRIM(A2). Then copy col E and overwrite col A with a paste
special as values. Then delete col E.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message