SOLVED

Excel help with vlookup and more

%3CLINGO-SUB%20id%3D%22lingo-sub-1495177%22%20slang%3D%22en-US%22%3EExcel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495177%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20trying%20to%20create%20an%20order%20form%20in%20Excel%20with%205%20columns.%20First%202%20columns%20are%20generic%20%3CSTRONG%3EItem%20No.%3C%2FSTRONG%3E%20and%20%3CSTRONG%3EQuantity%3C%2FSTRONG%3E.%20My%203rd%20Col.%20has%20%3CSTRONG%3EVendor%3C%2FSTRONG%3E%20information%20where%20a%20person%20can%20pick%20the%20vendor%20they%20want%20(drop%20down%20list).%20However%2C%20it%20also%20allows%20correct%20product%20info%20(items%20you%20can%20buy%20from%20that%20specific%20vendor)%20to%20popup%20in%20next%20column%20call%20%3CSTRONG%3EHardware%20Type%3C%2FSTRONG%3E.%20Example-if%20you%20have%20Apple%2C%20Samsung%2C%20Microsoft%20as%20vendor%20and%20you%20pick%20Microsoft%2C%20you%20will%20only%20see%20all%20the%20items%20you%20can%20purchase%20from%20Microsoft%20not%20from%20Apple%20or%20Samsung.%20This%20part%20is%20working%20fine%20and%20here%20is%20my%20problem----when%20I%20select%20an%20item%20in%26nbsp%3B%20%3CSTRONG%3EHardware%20Type%3C%2FSTRONG%3E%20column%2C%20I%20want%20to%20automatically%20popup%20%3CSTRONG%3EPart%20No%3C%2FSTRONG%3E.%20based%20on%20whatever%20hardware%20type%20person%20is%20selecting.%20Example-if%20I%20select%20Microsoft%20as%20vendor%20and%20in%20hardware%20type%20I%20select%20office%20365%2C%20I%20want%20to%20have%20office%20365%20part%20no%20or%20product%20code%20automatically%20popup%20in%20%3CSTRONG%3EPart%20No%3C%2FSTRONG%3E%20column.%20I%20can't%20get%20it%20working....any%20help%20in%20this%20matter%20would%20be%20greatly%20appreciated.%20if%20you%20need%20more%20info%2C%20let%20me%20know.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1495177%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1495244%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3BAttached.%20workbook%20with%20two%20possible%20solutions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20uses%20named%20ranges%20and%20INDIRECT.%20In%20the%20attached%20example%20the%20named%20ranges%20are%20static.%20Didn't%20go%20so%20far%20as%20to%20make%20them%20(more)%20dynamic%20since%20you%20indicated%20that%20you%20had%20everything%20working%20except%20for%20the%20part%20number%20lookup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20one%20uses%20new%20functions%20(UNIQUE%2C%20FILTER%2C%20XLOOKUP)%20and%20only%20works%20on%20the%20most%20recent%20version%20of%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1496141%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1496141%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20check%20your%20solution%20yet%20just%20got%20busy%20with%20work%20but%20I%20will%20check%20it%20out%20and%20let%20you%20know.%20I%20much%20appreciated%20your%20help%20and%20your%20quick%20reply.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1496824%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1496824%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esince%2C%20I%20am%20horrible%20with%20Excel%2C%20I%20am%20trying%20to%20understand%20your%20response%20and%20data%2Fform%20you%20sent%20to%20me.%20Thank%20you%20again%3C%2FP%3E%3CP%3EI%20am%20attaching%20my%20form%20in%20this%20well%20for%20you%20to%20take%20a%20look...%3C%2FP%3E%3CP%3Eas%20you%20can%20see%2C%20if%20you%20select%20CISCO%20for%20vendor%2C%20select%20any%20hardware%20they%20sell%2C%20you%20will%20see%20the%20part%20no%20will%20change%20accordingly.%20BUT%2C%20it%20only%20works%20with%20CISCO!!!%20if%20I%20select%20different%20vendor%2C%20it%20doesn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1497535%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497535%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3Eman%2C%20you%20are%20the%20master...you%20have%20solved%20my%20issue.%20I%20really%20want%20to%20thank%20you%20for%20your%20suggestion%20and%20attaching%20this%20file%20for%20me.%20it%20gave%20me%20god%20idea%20what%20I%20need%20to%20do.%20much%2C%20much%20appreciated...keep%20up%20the%20good%20work%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1497589%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1497589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3BGlad%20you%20could%20figure%20it%20out%20before%20I%20had%20a%20chance%20to%20respond%20to%20your%20previous%20post.%20In%20case%20you%20are%20using%20Windows365%20(previously%20known%20as%20Office365)%2C%20you%20may%20find%20the%20second%20solution%20more%20flexible%2C%20though.%20The%20functions%20used%20should%20become%20available%20to%20most%20subscribers%20in%20the%20coming%20weeks.%26nbsp%3B%3C%2FP%3E%3CP%3EImagine%20you%20have%20hundreds%20of%20vendors%20and%20thousands%20of%20parts.%20Working%20with%20named%20ranges%20will%20soon%20become%20difficult%2C%20if%20not%20unmanageable.%20The%20second%20option%20allows%20you%20to%20put%20all%20the%20vendor%20and%20parts%20information%20in%20one%20big%20table%2C%20and%20run%20dynamic%20valuation%20lists%20from%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502008%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502008%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20thank%20you%20for%20all%20the%20information.%20Of%20course%2C%20people%20were%20impressed%20when%20I%20showed%20them%20what%20I%20am%20working%20now....I%20was%20asked%20to%20add%20one%20more%20column%20in%20this%20form%20that%20can%20show%20current%20pricing%20information%20for%20the%20products.%20Example-Select-%26gt%3BVendor-%26gt%3BProduct-%26gt%3BPN%20pop%20ups%20and%20than%20pricing%20pop%20up%20column.%20Is%20this%20something%20I%20can%20do%20easily%20do%20I%20have%20edit%20your%20great%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502092%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502092%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3BWhich%20of%20the%20solutions%20works%20best%20for%20you%3F%26nbsp%3BCould%20be%20similar%20to%20a%20VLOOKUP%20as%20in%20the%20attached%20file.%20Have%20a%20look%20at%20the%20green%20shaded%20area%2C%20please.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502327%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502327%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20well%20that%20should%20work.....I%20am%20going%20to%20try%20the%20first%20solution%20which%20has%20very%20long%20formula%20starts%20with%20IFERROR.%20I%20will%20give%20it%20a%20try%20and%20let%20you%20know.%20Thanks%20again%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502357%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3DVLOOKUP(E2%2C%24P%249%3A%24Q%2416%2C2%2C0)%20what%20is%20%24P%249%20%24Q%2416%20means%3F%20how%20does%20this%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502374%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502374%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3Eactually%2C%20I%20think%20I%20figured%20it%20out.%20Just%20did%20a%20quick%20test%20and%20it%20works.%20I%20need%20to%20implment%20it%20in%20original%20form%20and%20see%20what%20happens.%20Thank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1502497%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20help%20with%20vlookup%20and%20more%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F712830%22%20target%3D%22_blank%22%3E%40gdubey%3C%2FA%3E%26nbsp%3BGreat!%20don't%20hesitate%20to%20come%20back%20here%20if%20you%20run%20into%20troubles.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, I am trying to create an order form in Excel with 5 columns. First 2 columns are generic Item No. and Quantity. My 3rd Col. has Vendor information where a person can pick the vendor they want (drop down list). However, it also allows correct product info (items you can buy from that specific vendor) to popup in next column call Hardware Type. Example-if you have Apple, Samsung, Microsoft as vendor and you pick Microsoft, you will only see all the items you can purchase from Microsoft not from Apple or Samsung. This part is working fine and here is my problem----when I select an item in  Hardware Type column, I want to automatically popup Part No. based on whatever hardware type person is selecting. Example-if I select Microsoft as vendor and in hardware type I select office 365, I want to have office 365 part no or product code automatically popup in Part No column. I can't get it working....any help in this matter would be greatly appreciated. if you need more info, let me know. Thanks

18 Replies
best response confirmed by gdubey (Occasional Contributor)
Solution

@gdubey Attached. workbook with two possible solutions.

 

The first uses named ranges and INDIRECT. In the attached example the named ranges are static. Didn't go so far as to make them (more) dynamic since you indicated that you had everything working except for the part number lookup.

 

The second one uses new functions (UNIQUE, FILTER, XLOOKUP) and only works on the most recent version of Excel.

@Riny_van_Eekelen 

I haven't check your solution yet just got busy with work but I will check it out and let you know. I much appreciated your help and your quick reply.

@Riny_van_Eekelen 

since, I am horrible with Excel, I am trying to understand your response and data/form you sent to me. Thank you again

I am attaching my form in this well for you to take a look...

as you can see, if you select CISCO for vendor, select any hardware they sell, you will see the part no will change accordingly. BUT, it only works with CISCO!!! if I select different vendor, it doesn't work.

@Riny_van_Eekelenman, you are the master...you have solved my issue. I really want to thank you for your suggestion and attaching this file for me. it gave me god idea what I need to do. much, much appreciated...keep up the good work

@gdubey Glad you could figure it out before I had a chance to respond to your previous post. In case you are using Microsoft365 (previously known as Office365), you may find the second solution more flexible, though. The functions used should become available to most subscribers in the coming weeks. 

Imagine you have hundreds of vendors and thousands of parts. Working with named ranges will soon become difficult, if not unmanageable. The second option allows you to put all the vendor and parts information in one big table, and run dynamic valuation lists from that.

@Riny_van_Eekelen  thank you for all the information. Of course, people were impressed when I showed them what I am working on....I was asked to add one more column in this form that can show current pricing information for the products. Example-Select->Vendor->Product->PN pop ups and than pricing pop up column. Is this something I can do easily do I have edit your great formula?

@gdubey Which of the solutions works best for you? Could be similar to a VLOOKUP as in the attached file. Have a look at the green shaded area, please.

 

@Riny_van_Eekelen  well that should work.....I am going to try the first solution which has very long formula starts with IFERROR. I will give it a try and let you know. Thanks again

@Riny_van_Eekelen=VLOOKUP(E2,$P$9:$Q$16,2,0) what is $P$9 $Q$16 means? how does this work?

@Riny_van_Eekelenactually, I think I figured it out. Just did a quick test and it works. I need to implment it in original form and see what happens. Thank you

@gdubey Great! don't hesitate to come back here if you run into troubles.

@Riny_van_EekelenI thought I was done with this but not yet....lol

so, I have this order form where people can go and select items they want to purchase etc. This part is working good now with your help. Now, they also want to SAVE copy of the order on different worksheet  (in same excel doc) so they can go back to it when items are received or they can look at if an item is order or not? Is this possible?

@Riny_van_Eekelensee attached what I created to simplify things

@gdubeymy plan is to create an order....somehow save it on different worksheet and when this order is received, I click on Mark as received button, go to this order and mark it.

@gdubey Should all be possible, but it would require som VBA coding. Difficult to do that without the real workbook to play with, though.

@Riny_van_EekelenI see....do you think recording a MACRO might work as well?

@gdubey Possibly! Give it a try. Record the steps you want to have executed automatically. Probably, you  need to clean-up the code afterwards, but it could be a good start. 

@Riny_van_EekelenYes I got it working. I appreciate your help.