MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Putting together an order form for a customer and I’ve reached a dead end. Basically I have two brands with a model each. Each model has a subset and different combinations of memory and colour. I just used a combination of a simple data validation drop down followed by a VLOOKUP and then a bunch of dependent drop downs.
For example A1 - Samsung or Apple from a simple drop down box
B1 - Returns Galaxy or iPhone from a VLOOKUP.
C1 - returns model based on a dependent drop down (5s, 6, S7 Edge etc)
D1 - As above for memory options
E1 - As above for colour options
Where I’ve come unstuck is how to get prices to appear based on the above. Is there something similar to a VLOOKUP that can return a price from a table based on a string of combinations as per the above? IF would be a no go due to the number of possible combinations.
Apologies if that doesn’t make any sense. Google is failing me and my excel knowledge isn’t great. Any help would be greatly appreciated
Concatenate your text in the same format as your price table
Concat(B1," ",C1," ",D1," ",E1)
Would return all the text with spaces between them for a compare/vlookup
Could you use concatenate on cells A1:E1 to create the vlookup search string for your price table?
Thanks guys. Not familiar with concatenate, I’ll try it with the formula builder in the morning
https://support.office.com/en-us/article/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d
That is the basics, but simply it joins up anything you put into the formula, so you can mix up cell refs and text by putting it between quotes, each item separated by a comma
I never bother with CONCAT(), just use & and quotation marks e.g.:
A1 & " " & B1 & " " & C1
You could also do something with SUMIFS -
Cost = SUMIFS ( column containing prices, column containing manufacturer, $A$1, column containing type, $B$1...)
That'd require entties to be unique otherwise you'd get the sum of all matching prices but you could chack that with a COUNTIFS before the SUMIFS to flag dupes -
=IF (COUNTIFS (....) <>1, "Lookup Error", SUMIFS (....) )
Thanks all. I already have the prices I just need them to auto fill based on the results of the other columns. I think SUMIFS would be too unwieldy. CONCAT is also of little use unfortunately. Is there anything similar to VLOOKUP that can take more than one reference in to account?
I basically have five columns:
Brand
Name
Model
Memory
Colour
The various combinations from these are populated by dependent dropdowns using date validation. The sixth column is Price and I just need a way to have it auto fill
I think i'd just use INDEX and MATCH rather than a lookup in this scenario. Afraid i've not the time now to write you an example formula https://exceljet.net/formula/index-and-match-with-multiple-criteria
I'd probably do a concatenation too (and not bother using the actual function, rather just use quotations). If you want to send me your sheet I'll take a look, not much else doing!
You must have a matrix of prices somewhere I presume? Can you ignore colour, as I assume that doesn't affect price?
Is there anything similar to VLOOKUP that can take more than one reference in to account?
VLOOKUP can if you use CONCATENATE as suggested e.g.
=VLOOKUP(CONCATENATE(A2,B2,C2,D2,E2),Sheet2!$F:$G,2,FALSE)
assuming you have a matrix of prices with similar columns for Brand, Name, etc you just need to add a column CONCATENATEing them into a key to use for the VLOOKUP
or use a database
Got it thanks to the help from you all. STE never fails to deliver 👍🏼