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:

Vendor	Moo Paper
Size	5 x 7
Finish	Matte or Gloss
Weight	16 pt

<tbody>
</tbody>

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.

Vendor	Size	Finish	Weight	Qty	Pricing	Price Per Unit
Moo Paper	5 x 7	Matte or Gloss	16 pt	25	$ 24.00	$ 0.96
Moo Paper	5 x 7	Matte or Gloss	16 pt	50	$ 48.00	$ 0.86
Moo Paper	5 x 7	Matte or Gloss	16 pt	100	$ 72.00	$ 0.72
Moo Paper	5 x 7	Matte or Gloss	16 pt	250	$ 240.00	$ 0.48
Moo Paper	5 x 7	Soft Touch	18 pt	25	$ 36.00	$ 1.44
Moo Paper	5 x 7	Soft Touch	18 pt	50	$ 65.00	$ 1.30
Moo Paper	5 x 7	Soft Touch	18 pt	100	$ 144.00	$ 1.09
Moo Paper	5 x7	Soft Touch	18 pt	250	$ 360.00	$ 0.72

<colgroup><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>

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