Mac

Ask Mac Questions and Get Answers from Experts ASAP

Ask a Computer Expert,
Get an Answer ASAP!

Mac

Need Excel formula to return results for more than 1 match

Customer Question
I have 3 tables. a...
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
Show More
Show Less
Ask Your Own Mac Question
Answered in 2 hours by:
4/1/2011
Mac Support Specialist: John D, BS Degree in Engineering replied 6 years ago
John D
John D, BS Degree in Engineering
Category: Mac
Satisfied Customers: 9,666
Experience: Worked on Macs and PC's for 20+ years.
Verified

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)

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
hi
I don't know how to send a file on this site
Mac Support Specialist: John D, BS Degree in Engineering replied 6 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

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
http://wikisend.com/download/560828/inventory_Update_Worksheet_test.xls
Mac Support Specialist: John D, BS Degree in Engineering 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?

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
yes
Mac Support Specialist: John D, BS Degree in Engineering 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

 

 

 

 

Ask Your Own Mac Question
Customer reply 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?
Mac Support Specialist: John D, BS Degree in Engineering 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

 

 

 

 

Ask Your Own Mac Question
Customer reply replied 6 years ago
this is going to be a bigger job then
Mac Support Specialist: John D, BS Degree in Engineering 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

 

 

Ask Your Own Mac Question
Ask Mike Your Own Question
Mike
Mike
Mike, Mac Medic
Category: Mac
Satisfied Customers: 8,555
8,555 Satisfied Customers
Experience: Over 20 years IT experience with Apple computers in publishing, marketing and design.

Mike is online now

A new question is answered every 9 seconds

How JustAnswer works:

  • Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction GuaranteeRate the answer you receive.

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

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!

FrankCanada

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 CustomerNew 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!!!!

AlexLos 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.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

< Previous | Next >

Meet the Experts:

Mike

Mike

Mac Medic

8,555 satisfied customers

Over 20 years IT experience with Apple computers in publishing, marketing and design.

Daniel

Daniel

Mac Genius

4,711 satisfied customers

Apple certified on desktop and portable, help desk qualified. Have owned and used Macs since 1989.

Vinod Menon

Vinod Menon

Support Specialist

2,394 satisfied customers

worked as a Tech support Associate for Apple products

John T. F.

John T. F.

Mac Druid

1,994 satisfied customers

20+ years in the computer/Mac industry

David

David

Mac Support Specialist

1,507 satisfied customers

BSc H.Dip Apple Certified Professional

Claws224

Claws224

IEEE Datacommunication Engineer

965 satisfied customers

IEEE, Microsoft

Mac Tech

Mac Tech

Apple Specialist

888 satisfied customers

7 + Years Supporting Apple Products

< Previous | Next >

Related Mac Questions
I cannot get into apps with my fingerprint. How can I just
I cannot get into apps with my fingerprint. How can I just use my pass code instead? … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
Cant get it to automaticlly turn off after call is finished.
cant get it to automaticlly turn off after call is finished … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I'm trying to work out how to transfer wav and mp3 files
I'm trying to work out how to transfer wav and mp3 files from my computer to my ipad pro … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I'm not able to upload an iMovie to my facebook account. I
I'm not able to upload an iMovie to my facebook account … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
Won;t accept my password. Iphone 7. I just realized when by
I just realized when by accident I deleted Zelle pay … read more
RussellA
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
I have PSE 12 on my Mac...have used it for years. All of a
I have PSE 12 on my Mac...have used it for years. All of a sudden, the organizer no longer shows the menu on the bottom so I can't get to the editor. What happened? How can I get the bottom menu back?… read more
Jinsmn
Jinsmn
Systems Administrator
Diplom
1,260 satisfied customers
I plug in my iPhone and nothing appears on the screen and it
I plug in my iPhone and nothing appears on the screen and it begins to beep continuously (the same beep it makes when I normally plug it in) … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I have I mac. I can't tell you it is the last one before
i can't tell you it is the last one before your current one but the problem is on start up it show me a folder with a question mark … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I cannot open micosoft outlook, a caption pops up saying
i cannot open micosoft outlook, a caption pops up saying there is an error … read more
Viet - Computer Tech
Viet - Computer Tech
Computer Technican
Bachelor\u0027s Degree
953 satisfied customers
My Mac is acting very strange. It is freezing up and it
My Mac is acting very strange. It is freezing up and it won't wake from the sleep mode correctly. It just restarts.… read more
Josh
Josh
It Support Specialist
Associate Degree
5,140 satisfied customers
I change my pass word and when i did I went back and I think
i change my pass word and when i did I went back and I think I forgo it … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I play the spite and malice game and when I click on it it
I play the spite and malice game and when I click on it it says welcome but the screen is black. … read more
Ashik
Ashik
Master\u0027s Degree
5,097 satisfied customers
I have been trying to verify my apple ID password for weeks
I have been trying to verify my apple ID password ***** weeks now; can you help? … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I am unable to buy gift cards for I tunes. I was hacked and
I am unable to buy gift cards for I tunes. I was hacked and I changed my password, but now I just get the ...we can't send it now...try again later message … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
My phone does not work since I upgraded. I can hear the
My phone does not work since I upgraded. I can hear the phone ring, but no message comes through. When I call out nothing happens. … read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
Ii have forgotten my computer password. Signing in. It is an
Ii have forgotten my computer password JA: What exactly are you having trouble accessing? Customer: Signing in JA: What's the brand and model of your computer? And the Operating System (OS)? Customer:… read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
I cannot access Mail on my iPad. It was fine yesterday but
I cannot access Mail on my iPad. It was fine yesterday but now comes up as a blank page. I changed my mail password ***** case that was the problem … read more
Vinod Menon
Vinod Menon
Support Specialist
Master's Degree
2,394 satisfied customers
I purchased a new Apple IPad yesterday and I can't remember
I purchased a new Apple IPad yesterday and I can't remember my password ***** open the I-Pad. Each time I try to open it I receive a message that the iPad is disabled and I must wait xxxx no. of minut… read more
Justin Johnson
Justin Johnson
Training and Development Contractor
1,104 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

Ask Your Question

x