vlookup question Urgent!!

  • Thread starter Thread starter J T
  • Start date Start date
J

J T

Suppose i have 2 sheets
In one sheet i have data as following

Others 10
X 23
Others 23
Phase 12

Now in sheet 2 i have

Others 33
X 23


In short in sheet i have to do a vlookup and if there are two matching
values it should sum the two values (As in 'Others' above)
 
A lookup returns only one value.
You could use one of these
=SUMIF(Sheet1!A1:A4,Sheet2!A1,Sheet1!B1:B4)
=SUMPRODUCT((Sheet1!A1:A4=Sheet2!A1)*Sheet1!B1:B4)

best wishes
 
Ok
I used the function
=SUMIF(Rough!B:B,"=Others*",Rough!C:C)
and got the result.
The reason i used Others* is because there are values with spaces.
Now my problem is i don't want to type in the value Others*. Suppose if
others is in cell A4,
i tried =SUMIF(Rough!B:B,"=*"& A4 &"*",Rough!C:C). But this give me the
first value and not the sum. Is it because the value in A4 is Others(without
space).
Can anyone tell me how can this be done.

thanks
 
I tried TRIM(Sheet!A1:A4) but Excel did not do the trimming so I looked more
closely at what you had sent (I had done a copy&paste from email to Excel).
Turns out that you test does not have trailing spaces but rather ends in
CHAR(160).

With this info I successfully used
=SUMPRODUCT(--(SUBSTITUTE(Sheet1!A1:A4,CHAR(160),"")=A1),Sheet1!B1:B4
 
Back
Top