Comparing and returning values based on multiple criteria

neal.clarke182

New Member
Joined
Jan 19, 2012
Messages
8
Hi All,

Please accept my apologies if this has been answered however, I have spent hours looking for a resolution to this but am yet to find anything that will work.

So what I am looking to achieve is something like the logic below.

1. I want a sheet that looks like the below, to have the unit price column populated.
VJ.xls
GHIJ
6TOTALManufacturer NameManufacturer Part NumberUnit Price
72050WALSIN0402B104K160CT
8700TAIYO YUDENEMK107ABJ475KA-T
950KEMETC0402C103K5RACTU
10100KEMETC0805C226M8PACTU
11400TDKC1608X5R1A106M080AC
12100MULTICOMP PROMCRH35V337M10X16
13350YAGEOCC0603ZRY5V9BB104
14200KEMETC0805C224K5RACTU
1550PANASONICEEUFM1V221L
1650KEMETC0805C103K1RACTU
Sheet1

2. Look up a reference (Column I in the above) that potentially appears in multiple worksheets. Lets say i have 4 sheets but the reference is only in 3 of them (2 sheets as below with an item highlighter red as an example).
Bom_8571752.xlsx
ABCDE
1Manufacturer Part NumberManufacturerQuantityUnit PriceQuantity Available
20402B104K160CTWalsin Technology Corporation20500.0040822521244
3EMK107ABJ475KA-TTaiyo Yuden7000.0542279684
4C0402C103K5RACTUKEMET500.0076828308
5C0805C226M8PAC7800KEMET1000.094735237
6C1608X5R1A106M080ACTDK Corporation4000.089714635
7MCRH35V337M10X16
8CC0603ZRY5V9BB104Yageo3500.008214081751
9C0805C224K5RACTUKEMET2000.0235226678
10EEUFM1V221L
11C0805C103K1RACTUKEMET500.0134958398
Sheet1
VJ.xls
ABCDE
1Manufacturer NameManufacturer Part NumberQuantityPriceAvailability
2WALSIN0402B104K160CT20500.0033057363
3TAIYO YUDENEMK107ABJ475KA-T7000.04200278440
4KEMETC0402C103K5RACTU500.00700124194
5KEMETC0805C226M8PACTU1000.136004035
6TDKC1608X5R1A106M080AC4000.0731014438
7MULTICOMP PROMCRH35V337M10X161000.0842016118
8YAGEOCC0603ZRY5V9BB1043500.00590136000
9KEMETC0805C224K5RACTU2000.0215032495
10PANASONICEEUFM1V221L500.123004635
11KEMETC0805C103K1RACTU500.0072034630
Bom Worksheet

2. When the reference is found (column A in sheet1 and B in sheet 2), return a lowest price (out of the the prices found which could be more than these two) from another cell on that sheet (column D in both examples). This must only happen if the stock quantity available (column E) is higher than the quantity i want (Column C).
3. If the lowest price found from the above does not have the quantity required (or greater) return the next lowest price that has the stock (or greater) available.
Based on the example sheets I want to see cell D2 from the third Sheet populate J2 in the first sheet.

Ideally I am looking to get something that does this for 3 or 4 sheets but can be extrapolated out to a max of 10 sheets.
Hopefully this makes some level of sense but if not I am more than happy to try to help explain in great detail or some way that may make more sense.

I promise this makes sense in my head and i have 100% seen this done but cannot work it out as a whole (i can almost do each step individually at the same time as the other steps).
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
It makes sense in my head too, but some questions:-
- Are your 4 x "supplier" spreadsheets always in one of the two formats
- Why are your required quantities also the "Quantity" column in the "supplier" spreadsheets
- Presume that the "Quantity Available" in the "supplier" spreadsheets are the quantities that they currently hold
 

Brunts

Board Regular
Joined
May 23, 2008
Messages
79
Office Version
  1. 2016
Platform
  1. Windows
Think your going to have to break it down into two separate steps - first vlookup the quantities and prices from your suppliers, catering for not in stock values, and put them on your VJ.xls sheet - hide them if you must and then use an array formula to look up min price, if min quantities are in stock - also lets you add in more suppliers

Excel Forum.xlsx
ABCDEFGHIJKLM
1TOTALManufacturer NameManufacturer Part NumberUnit PriceB-PriceC-PriceD-PriceE-PriceB-QuantityC-QuantityD-QuantityE-Quantity
22050WALSIN0402B104K160CT0.0020.0040.0030.0020.0012050205020502049
3700TAIYO YUDENEMK107ABJ475KA-T0.0030.0050.0040.0030.002700700700699
450KEMETC0402C103K5RACTU0.0040.0060.0050.0040.00350505049
5100KEMETC0805C226M8PACTU0.006#N/A0.006#N/A0.0040100099
6400TDKC1608X5R1A106M080AC0.0060.0080.0070.0060.005400400400399
7100MULTICOMP PROMCRH35V337M10X160.00700.0080.0070.006010010099
8350YAGEOCC0603ZRY5V9BB1040.0080.010.0090.0080.007350350350349
9200KEMETC0805C224K5RACTU0.0090.0110.010.0090.008200200200199
1050PANASONICEEUFM1V221L0.0100.0110.010.0090505049
1150KEMETC0805C103K1RACTU0.0110.0130.0120.0110.0150505049
SheetA
Cell Formulas
RangeFormula
F2:F11F2=VLOOKUP(C2,SheetB!$A$2:$E$11,4,FALSE)
G2:G11G2=VLOOKUP(C2,SheetC!$B$2:$E$11,3,FALSE)
H2:H11H2=VLOOKUP(C2,SheetD!$A$2:$E$11,4,FALSE)
I2:I11I2=VLOOKUP(C2,SheetE!$B$2:$E$11,3,FALSE)
J2:J11J2=IFERROR(VLOOKUP(C2,SheetB!$A$2:$E$11,5,FALSE),0)
K2:K11K2=IFERROR(VLOOKUP(C2,SheetC!$B$2:$E$11,4,FALSE),0)
L2:L11L2=IFERROR(VLOOKUP(C2,SheetD!$A$2:$E$11,5,FALSE),0)
M2:M11M2=IFERROR(VLOOKUP(C2,SheetE!$B$2:$E$11,4,FALSE),0)
D2:D11D2=MIN(IF(J2:M2>=A2, F2:I2))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,921
Messages
5,639,011
Members
417,062
Latest member
CM214

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top