Jobs Simply

how to use vlookup for multiple lookup values?

my problem is that vlookup does not get the results i want. I have a list of data for 2 months of vendors and what they owe in different categories (services, supplies, etc). i then have a identification number and a job number for every vendor. I want to use vlook up to sum how much each vendor owes my company for the two months. I do not want to merge the files together. I tried using vlookup with just the id number, but it does not give me the data i want since the vendor number is the same for alot of jobs and vice versa. I was wondering if there is a way to use both the vendor number and the job number as the lookup value. I do not want to use the conecate function cause it will not give me the second occurence of the value (i keep getting the first occurence and not having the rest of the occruences in the vlookup) the sumif function would take too long to do and i need to create a report that uses outstanding vendors in april and find their balances in may also there are several ones with both the same vendor id and job id

Public Comments

1. I don't think you can do it with the vlookup, because that stops when it finds the first matching cell, but I suggest trying the sumif function. using the function wizard, range would be the cells you want evaluated, which in your case would be the vendor ID number. Criteria would be the cell of the vendor ID you want to look up. You could either type this into the formula or link it to a cell. Finally, sum_range would be the cells you would want to add together, which would be the column of the amount owed to you. I'm guessing you have something that looks like this: Column A | Column B | Column C | Column D | Vendor ID | Job ID | Services | Supplies | If that's the case, and you wanted to add up both Columns C and Column D for one single Vendor ID, the formula would have to look like this: =SUMIF(A2:A100,E1,C2:C100)+ SUMIF(A2:A100,E1,D2:D100) Where E1 is to the right of your table and where you will enter the vendor you want to add up. Edit: I'm not sure what you mean by would take too long to set up. Use the $ to keep the ranges the same if you need to copy and paste it around: =SUMIF($A$2:$A$100,E1,$C$2:$C$100)
2. i am unclear about not wanting to merge the files together. but besides that...have you considered a pivot table? it sounds like it will work. autofilter might also work. finally you might want to look into the SUMPRODUCT() formula...see source site. if you put up a sample (4 or 5 rows), i could write a formula. just some ideas.