Is there a way to use =VLOOKUP to average this way?

[JLAKS11]

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L
1	MY ID#	DISTRIBUTER #1 SKU	DISTRIBUTER #1 STOCK	DISTRIBUTER #1 PRICE	DISTRIBUTER #2 SKU	DISTRIBUTER #2 STOCK	DISTRIBUTER #2 PRICE	DISTRIBUTER #3 SKU	DISTRIBUTER #3 STOCK	DISTRIBUTER #3 PRICE	AVAILABILITY	AVERAGED PRICE
2	159	596920	instock	10.99				42890	instock	8.99	instock	9.99
3	162	717061	outofstock	15.99	880-HS3513	outofstock	18.99				outofstock	n/a
4	164	717073	instock	4.99	880-HS4513	instock	7.99				instock	6.49
5	166				880-HS513	instock	11.99				instock	11.99
6	168	596922	instock	6.99				40963	instock	4.99	instock	5.99
7	170	596924	outofstock	21.99				40965	outofstock	19.99	outofstock	n/a
8	172				880-HS1459	instock	8.99	40964	outofstock	6.99	instock	8.99
9	174	597712	outofstock	6.99	253-AHSR8	instock	9.99	40966	outofstock	5.99	instock	9.99
10	176	597697	instock	12.99	253-AHSR4	outofstock	15.99	37605	instock	11.99	instock	12.49
11	178	597724	outofstock		253-AHSR75	instock	4.99	40443	outofstock	3.99	instock	4.99
12	180				253-AHSR1	instock	7.99				instock	7.99
13	182	597715	instock	17.99	253-AHSR9	instock	20.99	42865	instock	18.99	instock	19.32
14	184	596908	instock	8.99				42866	instock	9.99	instock	9.49
15	186	596910	instock	5.99							instock	5.99
16	188	596904	instock	28.99							instock	28.99
17	190	589259	outofstock	23.99							outofstock	n/a
18	192	596906	instock	14.99				37598	outofstock	13.99	instock	14.49
19	194	723694	outofstock	2.99				45266	outofstock	3.99	outofstock	n/a

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Hi Everyone,

Been using Excel a bit now with =VLOOKUP and tables. My dilemma is trying to automate sample above sheet for a very large spreedsheet (thousands of rows). Unfortunately being to much of a newbee, only know basics of =VLOOKUP, so a more complex formula if possible to do what I need is beyond my capabilities.

First =VLOOKUP: As in above sample for AVAILABILITY. It will search DISTRIBUTOR 1,2 & 3's stock column and if it finds it instock in any of the columns it will display that in AVAILABILITY column and vice versus for if no column has instock (display outofstock).

Second =VLOOKUP: This one would be a bit more complex and have to do with generating an averaged price of all DISTRIBUTER price columns (in AVERAGED PRICE column) that show instock and dis-regard any prices of distributors that show outofstock.

Any help on this will be much appreciated.

 

[wynn89]

Hi,

Can i safely assume that there will only be a maximum of 3 distributors for each ID#?

Regards,
Wynn

 

[JLAKS11]

Hi Wynn,

Thank you for replying.

For purposes of displaying a simpler sheet I used 3. It would actually be a total of 5. Would more than 3 be a deal breaker for Excel =VLOOKUP?

 

[SpillerBD]

For K2:
{=IF(OR(C2="instock",F2="instock",I2="Instock"),"INSTOCK","Out Of Stock")}
For L2:
=AVERAGEIF((C2:F2:I2),"Instock",(D2:G2:J2))

The {...} expression is an array formula. You do not enter the "{" "}" characters but after typing he expression enter it by CTRL+SHIFT+ENTER.

 

[JLAKS11]

Hi Spiller,

Thank you for replying.

This is exactly what I needed and it worked perfectly. It works better than what I was trying to accomplish with =VLOOKUP because it will save me more time by not having to create additional sheets and tables.

My hat's of to you Spiller and the Mr. Excel forum. I use other forums for various different projects. By far, I have had the most pleasurable and informative experience here than out of all combined. Members here are the most informed, curtious and responsive I have found. These boards are truly something special.

Spiller and Mr. Excel you ROCK!!!!!!