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
159	596920	instock	10.99				42890	instock	instock
162	717061	outofstock	15.99	880-HS3513	outofstock	18.99	outofstock	n/a
164	717073	instock	4.99	880-HS4513	instock	7.99	instock
166				880-HS513	instock	11.99	instock
168	596922	instock	6.99				40963	instock	instock
170	596924	outofstock	21.99				40965	outofstock	outofstock	n/a
172				880-HS1459	instock	8.99	40964	outofstock	instock
174	597712	outofstock	6.99	253-AHSR8	instock	9.99	40966	outofstock	instock
176	597697	instock	12.99	253-AHSR4	outofstock	15.99	37605	instock	instock
178	597724	outofstock		253-AHSR75	instock	4.99	40443	outofstock	instock
180				253-AHSR1	instock	7.99		instock
182	597715	instock	17.99	253-AHSR9	instock	20.99	42865	instock	instock
184	596908	instock	8.99	42866	instock	instock
186	596910	instock	5.99	instock
188	596904	instock	28.99	instock
190	589259	outofstock	23.99	outofstock	n/a
192	596906	instock	14.99	37598	outofstock	instock
194	outofstock	45266	outofstock	outofstock	n/a

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #9BC2E6"]MY ID#[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #1 SKU[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #1 STOCK[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #1 PRICE[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #2 SKU[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #2 STOCK[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #2 PRICE[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #3 SKU[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #3 STOCK[/TD]
[TD="bgcolor: #9BC2E6"]DISTRIBUTER #3 PRICE[/TD]
[TD="bgcolor: #9BC2E6"]AVAILABILITY[/TD]
[TD="bgcolor: #9BC2E6"]AVERAGED PRICE[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]8.99[/TD]

[TD="align: right"]9.99[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6.49[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]11.99[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4.99[/TD]

[TD="align: right"]5.99[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]19.99[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]6.99[/TD]

[TD="align: right"]8.99[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]5.99[/TD]

[TD="align: right"]9.99[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]11.99[/TD]

[TD="align: right"]12.49[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3.99[/TD]

[TD="align: right"]4.99[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7.99[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]18.99[/TD]

[TD="align: right"]19.32[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9.99[/TD]

[TD="align: right"]9.49[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5.99[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]28.99[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]13.99[/TD]

[TD="align: right"]14.49[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]723694[/TD]

[TD="align: right"]2.99[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.99[/TD]

</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!!!!!!