How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Mike Your Own Question
Mike, Mac Medic
Category: Mac
Satisfied Customers: 8527
Experience:  Over 20 years IT experience with Apple computers in publishing, marketing and design.
Type Your Mac Question Here...
Mike is online now
A new question is answered every 9 seconds

Need Excel formula to return results for more than 1 match

Customer Question

I have 3 tables. a list of my products, a list of inventory from a supplier where the product ID does not (necessarily) match my product ID, and a list of alternate product IDs for each of my product IDs (2 columns one with my product ID and one with possible supplier product IDs). the last one is like a key table which will allow me to match my products with the supplier's products. the problem is, if I use a (nested) vlookup formula to find the alternate product ID in table 3 and then look it up on the suppliers inventory table, it will only match the first alternate on table 3 and if there is no result, it won't try and see if there is a match for the second alternate ID for that product, or the third, etc.. Can someone help me? thanks Steve<br/><br/>ps<br/>here is a tiny sample of the tables<br/><br/>table 1<br/>My product ID                 + formula to return inventory qty<br/>2K4-A1005                      |<br/>2K4-A1005-10-PACK      |<br/><br/><br/><br/><br/>table 2<br/>supplier product ID    + Quantity<br/>A10005                      |     32<br/><br/><br/>table 3<br/>My product ID                 + Alternate product ID<br/>2K4-A1005                      |    A1005<br/>2K4-A1005                      |    1005<br/>2K4-A1005                      |    A105<br/>2K4-A1005-10-PACK      |    1005<br/>2K4-A1005-10-PACK      |    A1005<br/>2K4-A1005-10-PACK      |    A105
Submitted: 6 years ago.
Category: Mac
Expert:  John D replied 6 years ago.

Hi Steve,


Could you please send me the file so I can see the data layout and try to set up the formulas on it (let me know if you are not familiar with sending files on this site)




Customer: replied 6 years ago.
I don't know how to send a file on this site
Expert:  John D replied 6 years ago.

Ok, go to and upload the file there (no need to sign up). You will then get a page that has the download link and File ID. Copy the download link or the File ID and come back here and paste it in your reply.


If the file has sensitive information let me know before you upload it



Customer: replied 6 years ago.
Expert:  John D replied 6 years ago.

I got the file, thanks


Since your formulas are under the 'qty' column, I will assume that you want the formula to return the quantity from column B on 'BRC' where your product matches supplier product on the 'ALT' sheet. Correct?



Customer: replied 6 years ago.
Expert:  John D replied 6 years ago.

Ok, since there are multiple occurrences of your product on the ALT sheet you will need a macro to pull them all. I can write the macro for that which can be run on demand by say clicking a button. Would that be ok





Customer: replied 6 years ago.
my workbook is much more compicated than this sample workbook. there are 22 sheets and multiple named ranges and I need to perform this lookup across multiple tables (one for each supplier) and then add the results together. will your solution only work for the sample I have given you?
Expert:  John D replied 6 years ago.

Any solution, formulas or macro, will work only on the file that it is designed to work on. You chose to send me that file so I assume you wanted the solution for that file





Customer: replied 6 years ago.
this is going to be a bigger job then
Expert:  John D replied 6 years ago.

Yes with that many more sheets it is bound to be a bigger job. If macro is an acceptable option for you, you can send me the actual file and I will have a look and let you



Related Mac Questions