VLOOKUP question - multiple cell criteria?

[jaydaniel05]

Hi all,

I was hoping you guys could assist. I am trying to make a spreadsheet for my wife who is starting her own business.

I was hoping to use Data Validation (List) for her to choose what she is looking for:
[TABLE="width: 200"]
<tbody>[TR]
[TD]Vendor[/TD]
[TD]Moo Paper[/TD]
[/TR]
[TR]
[TD]Size[/TD]
[TD]5 x 7[/TD]
[/TR]
[TR]
[TD]Finish[/TD]
[TD]Matte or Gloss[/TD]
[/TR]
[TR]
[TD]Weight[/TD]
[TD]16 pt[/TD]
[/TR]
</tbody>[/TABLE]
We're going to have multiple vendors, sizes, finishes, and weight. My hope was to have the different quantities (25, 50, 100, 250) with its corresponding attributes + price shown.


[TABLE="width: 545"]
<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Size[/TD]
[TD]Finish[/TD]
[TD]Weight[/TD]
[TD]Qty[/TD]
[TD]Pricing[/TD]
[TD]Price Per Unit[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Matte or Gloss[/TD]
[TD]16 pt[/TD]
[TD="align: right"]25[/TD]
[TD] $ 24.00[/TD]
[TD] $ 0.96[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Matte or Gloss[/TD]
[TD]16 pt[/TD]
[TD="align: right"]50[/TD]
[TD] $ 48.00[/TD]
[TD] $ 0.86[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Matte or Gloss[/TD]
[TD]16 pt[/TD]
[TD="align: right"]100[/TD]
[TD] $ 72.00[/TD]
[TD] $ 0.72[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Matte or Gloss[/TD]
[TD]16 pt[/TD]
[TD="align: right"]250[/TD]
[TD] $ 240.00[/TD]
[TD] $ 0.48[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Soft Touch[/TD]
[TD]18 pt[/TD]
[TD="align: right"]25[/TD]
[TD] $ 36.00[/TD]
[TD] $ 1.44[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Soft Touch[/TD]
[TD]18 pt[/TD]
[TD="align: right"]50[/TD]
[TD] $ 65.00[/TD]
[TD] $ 1.30[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x 7[/TD]
[TD]Soft Touch[/TD]
[TD]18 pt[/TD]
[TD="align: right"]100[/TD]
[TD] $ 144.00[/TD]
[TD] $ 1.09[/TD]
[/TR]
[TR]
[TD]Moo Paper[/TD]
[TD]5 x7[/TD]
[TD]Soft Touch[/TD]
[TD]18 pt[/TD]
[TD="align: right"]250[/TD]
[TD] $ 360.00[/TD]
[TD] $ 0.72[/TD]
[/TR]
</tbody>[/TABLE]


I tried using a VLOOKUP formula with helper columns but that didn't do the trick. If anyone knows of a formula for it to just pull from my current example, I would truly, greatly appreciate it.
Thanks so much!!

 

[Kamolga]

Hi,

It is a bit complicated to describe, need to set the data properly.
As an example, I made a tab "Lists" where you should update Cendor, size, Finish, Weight and quantities and another sheet with corresponding price per unit.

You then have the formula sheet where you have drop lists (yellow cells) and infos out of them (blue cells).

The file is shared here through Onedrive
https://1drv.ms/x/s!AvmGsNl7aaaAgtJw3xbStArMHoR8rg.

It will open with Excel online (no lists). By clicking the 3 dots on top right and downloading the file you should be able to copy-paste and modify towards your project.

(Note that the lists are automatically extended, so if you add a vendor below Moo Paper, your list of vendors will automatically grow but the lists are not linked, so you can select Matte or Gloss with weight 18 even though it does not exist. In such case the price is empty).

 

[jaydaniel05]

WOW, thank you SO much!! I guess I wasn't even in the right ballpark of what formula to use. I can not thank you enough!!

 

[Kamolga]

You are welcome. Thank you for the feedback