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
Mike, Mac Medic
Category: Mac
Satisfied Customers: 6654
Experience:  Over 20 years IT experience with Apple computers in publishing, marketing and design.
25267123
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: 3 years ago.
Category: Mac
Expert:  John D replied 3 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 3 years ago.
hi
I don't know how to send a file on this site
Expert:  John D replied 3 years ago.

Ok, go to www.wikisend.com 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 3 years ago.
http://wikisend.com/download/560828/inventory_Update_Worksheet_test.xls
Expert:  John D replied 3 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 3 years ago.
yes
Expert:  John D replied 3 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 3 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 3 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 3 years ago.
this is going to be a bigger job then
Expert:  John D replied 3 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

 

 

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
< Last | Next >
  • Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill Bill M. Schenectady, New York
  • The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you! Frank Canada
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
 
 
 

Meet The Experts:

 
 
 
  • Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
< Last | Next >
  • http://ww2.justanswer.com/uploads/macthelife/2009-10-20_1899_mikesebaharsquare64.jpg Mike's Avatar

    Mike

    Mac Medic

    Satisfied Customers:

    6256
    Over 20 years IT experience with Apple computers in publishing, marketing and design.
  • http://ww2.justanswer.com/uploads/AS/ashiknasameen/2012-5-15_141836_final2.64x64.jpg Ashik's Avatar

    Ashik

    Mac Helper

    Satisfied Customers:

    5282
    7+ Years of Experience in troubleshooting Macs, iPhone, iPad, iPod etc
  • http://ww2.justanswer.com/uploads/DP/dpean/2012-6-6_172828_avatorme1.64x64.JPG Daniel's Avatar

    Daniel

    Mac Genius

    Satisfied Customers:

    4670
    Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.
  • http://ww2.justanswer.com/uploads/VI/vinodvmenon2005/1.64x64.jpg Vinod Menon's Avatar

    Vinod Menon

    Support Specialist

    Satisfied Customers:

    2068
    worked as a Tech support Associate for Apple products
  • http://ww2.justanswer.com/uploads/BE/beboo/2011-1-14_201648_n5063313142021801763.64x64.jpg Brandon M.'s Avatar

    Brandon M.

    Mac Support Specialist

    Satisfied Customers:

    1501
    10+ Years Mac Support as contractor and currently an IT Manager for law firm
  • http://ww2.justanswer.com/uploads/MA/MacDruid/IMG_0232.64x64.JPG John T. F.'s Avatar

    John T. F.

    Mac Druid

    Satisfied Customers:

    1408
    20+ years in the computer/Mac industry
  • http://ww2.justanswer.com/uploads/MA/MacHelpdesk/1d2d506.64x64.jpg David's Avatar

    David

    Mac Support Specialist

    Satisfied Customers:

    1236
    BSc, H.Dip, Apple Certified
 
 
 
Chat Now With A Mac Support Specialist
Mike
Mike
Mac Support Specialist
6654 Satisfied Customers
Over 20 years IT experience with Apple computers in publishing, marketing and design.