Comparing and returning values based on multiple criteria

[neal.clarke182]

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
	G	H	I	J
6	TOTAL	Manufacturer Name	Manufacturer Part Number	Unit Price
7	2050	WALSIN	0402B104K160CT
8	700	TAIYO YUDEN	EMK107ABJ475KA-T
9	50	KEMET	C0402C103K5RACTU
10	100	KEMET	C0805C226M8PACTU
11	400	TDK	C1608X5R1A106M080AC
12	100	MULTICOMP PRO	MCRH35V337M10X16
13	350	YAGEO	CC0603ZRY5V9BB104
14	200	KEMET	C0805C224K5RACTU
15	50	PANASONIC	EEUFM1V221L
16	50	KEMET	C0805C103K1RACTU
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
	A	B	C	D	E
1	Manufacturer Part Number	Manufacturer	Quantity	Unit Price	Quantity Available
2	0402B104K160CT	Walsin Technology Corporation	2050	0.00408	22521244
3	EMK107ABJ475KA-T	Taiyo Yuden	700	0.0542	279684
4	C0402C103K5RACTU	KEMET	50	0.0076	828308
5	C0805C226M8PAC7800	KEMET	100	0.0947	35237
6	C1608X5R1A106M080AC	TDK Corporation	400	0.089	714635
7	MCRH35V337M10X16
8	CC0603ZRY5V9BB104	Yageo	350	0.0082	14081751
9	C0805C224K5RACTU	KEMET	200	0.0235	226678
10	EEUFM1V221L
11	C0805C103K1RACTU	KEMET	50	0.0134	958398
Sheet1

VJ.xls
	A	B	C	D	E
1	Manufacturer Name	Manufacturer Part Number	Quantity	Price	Availability
2	WALSIN	0402B104K160CT	2050	0.00330	57363
3	TAIYO YUDEN	EMK107ABJ475KA-T	700	0.04200	278440
4	KEMET	C0402C103K5RACTU	50	0.00700	124194
5	KEMET	C0805C226M8PACTU	100	0.13600	4035
6	TDK	C1608X5R1A106M080AC	400	0.07310	14438
7	MULTICOMP PRO	MCRH35V337M10X16	100	0.08420	16118
8	YAGEO	CC0603ZRY5V9BB104	350	0.00590	136000
9	KEMET	C0805C224K5RACTU	200	0.02150	32495
10	PANASONIC	EEUFM1V221L	50	0.12300	4635
11	KEMET	C0805C103K1RACTU	50	0.00720	34630
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).

 

[Brunts]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	TOTAL	Manufacturer Name	Manufacturer Part Number	Unit Price		B-Price	C-Price	D-Price	E-Price	B-Quantity	C-Quantity	D-Quantity	E-Quantity
2	2050	WALSIN	0402B104K160CT	0.002		0.004	0.003	0.002	0.001	2050	2050	2050	2049
3	700	TAIYO YUDEN	EMK107ABJ475KA-T	0.003		0.005	0.004	0.003	0.002	700	700	700	699
4	50	KEMET	C0402C103K5RACTU	0.004		0.006	0.005	0.004	0.003	50	50	50	49
5	100	KEMET	C0805C226M8PACTU	0.006		#N/A	0.006	#N/A	0.004	0	100	0	99
6	400	TDK	C1608X5R1A106M080AC	0.006		0.008	0.007	0.006	0.005	400	400	400	399
7	100	MULTICOMP PRO	MCRH35V337M10X16	0.007		0	0.008	0.007	0.006	0	100	100	99
8	350	YAGEO	CC0603ZRY5V9BB104	0.008		0.01	0.009	0.008	0.007	350	350	350	349
9	200	KEMET	C0805C224K5RACTU	0.009		0.011	0.01	0.009	0.008	200	200	200	199
10	50	PANASONIC	EEUFM1V221L	0.01		0	0.011	0.01	0.009	0	50	50	49
11	50	KEMET	C0805C103K1RACTU	0.011		0.013	0.012	0.011	0.01	50	50	50	49
SheetA

Cell Formulas
Range	Formula
F2:F11	F2	=VLOOKUP(C2,SheetB!$A$2:$E$11,4,FALSE)
G2:G11	G2	=VLOOKUP(C2,SheetC!$B$2:$E$11,3,FALSE)
H2:H11	H2	=VLOOKUP(C2,SheetD!$A$2:$E$11,4,FALSE)
I2:I11	I2	=VLOOKUP(C2,SheetE!$B$2:$E$11,3,FALSE)
J2:J11	J2	=IFERROR(VLOOKUP(C2,SheetB!$A$2:$E$11,5,FALSE),0)
K2:K11	K2	=IFERROR(VLOOKUP(C2,SheetC!$B$2:$E$11,4,FALSE),0)
L2:L11	L2	=IFERROR(VLOOKUP(C2,SheetD!$A$2:$E$11,5,FALSE),0)
M2:M11	M2	=IFERROR(VLOOKUP(C2,SheetE!$B$2:$E$11,4,FALSE),0)
D2:D11	D2	=MIN(IF(J2:M2>=A2, F2:I2))
Press CTRL+SHIFT+ENTER to enter array formulas.