Vlookup multiple values

S

suraneniaditya

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
 
M

Max

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
 
S

suraneniaditya

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
 
M

Max

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

Max

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)))
Click to expand...

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.

---
 
Top