Using vlookup in case vlookup value is coming more than once in table array

[Ajay Gupta]

Hi All,


I am facing a problem while applying vlookup.

When we apply vlookup to a data then if in the table array (i.e. the column from which the data is to be vlookedd up) if lookup value is coming more than once then vlookup picks up the value of the first line item only.Is there a way where we can get the sum of both the line items.

For Ex-If from a salary data, salary of Ram is to be vlooked up from a table array wherein Ram is coming more than once,then if I apply vlookup then it will take that salary of RAM which is coming first in the list.Now my query is,is there any way I can have sum of all the salary of RAM appearing in that list.

 

[Colin Legg]

Yes, you can use the SUMIF() function instead of VLOOKUP().


=SUMIF(A2:A100,"RAM",B2:B100)


Where A2:A100 may contain the word "RAM" and B2:B100 contains the values you want to sum.

 

[bobhc]

Good day Ajay Gupta


You may find this workbook of help, it shows two methods of using a lookup to get more than one piece of data just by changing the name.


https://dl.dropbox.com/u/34893656/Multi_Vlookup-1.xlsx

 

[Faseeh]

Hi Ajay Gupta,


This is with refrence to the file uploaded by bobhc, the values in the table (The Order Amount) can be summed up for "Flores, Tia" using this formula:


=SUM(IFERROR((SEARCH("Tia",Table1[Salesperson])>=1)*Table1[Order Amount],0))


Press Ctrl+Shift+Enter


Regards,

Faseeh

 

[bobhc]

Good day Faseeh


Am I reading Ajay Gupta post wrong and missing the point (probable yes) why not just use the drop down to pick name ?

 

[Debraj]

Hi Ajay,


You can use Sumproduct too..

=SUMPRODUCT(($A$2:$A$100="Ram") * $B$2:$B$100)

Regards,

Deb