*DESPERATE* Need help immediately with vlookup

[sixsteps268]

Many many thanks in advance for what I've been told by other users, will be a big sucess in asking you experts for help! So needless to say I'm a first timer here on Mr. Excel.

What I need to do is return a value from a table based upon 3 variables. Variable 1 has 2 options, Variable 2 has 4 options, and Variable 3 has 9 options.

So my table has obviously 4 columns and I need to be able to return the value based upon the selection of variable 1, then by variable 2, and finally by variable 3. Does that make sense?

I've tried several things ranging from if and nested vlookups to a choose function and I can't seem to get it right.

Someone please help rescue me! This is for my boss and a big presentation!

Thanks!

 

[jimmy90]

Many many thanks in advance for what I've been told by other users, will be a big sucess in asking you experts for help! So needless to say I'm a first timer here on Mr. Excel.

What I need to do is return a value from a table based upon 3 variables. Variable 1 has 2 options, Variable 2 has 4 options, and Variable 3 has 9 options.

So my table has obviously 4 columns and I need to be able to return the value based upon the selection of variable 1, then by variable 2, and finally by variable 3. Does that make sense?

I've tried several things ranging from if and nested vlookups to a choose function and I can't seem to get it right.

Someone please help rescue me! This is for my boss and a big presentation!

Thanks!

More details would allow us to give you a much better answer.

With the information given i would suggest using nested Index,Match function to look up columns and rows for matches. but without more details, Values (atleast sample values) and Ranges (atleast sample ranges) it would be hard to give you much more advice for your question.

 

[sixsteps268]

More details would allow us to give you a much better answer.

With the information given i would suggest using nested Index,Match function to look up columns and rows for matches. but without more details, Values (atleast sample values) and Ranges (atleast sample ranges) it would be hard to give you much more advice for your question.

Sure, let me see if I can be more specific for you.

Here's my table. I need to return the value in the amount column. The facility, LOC, and Insurance values are in a validation drop down field. Does that make more sense? So I need a formula to be able to return the value after selecting a facility, LOC, and Insurance Provider.
[TABLE="width: 264"]
<!--StartFragment--> <colgroup><col width="66" span="4" style="width:66pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 66"]Facility [/TD]
[TD="class: xl66, width: 66"]LOC[/TD]
[TD="class: xl66, width: 66"]Insurance[/TD]
[TD="class: xl67, width: 66"]Amount[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]Cigna[/TD]
[TD="class: xl63, align: right"]750[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]BCBS[/TD]
[TD="class: xl63, align: right"]572[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]Compsych[/TD]
[TD="class: xl63, align: right"]901[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]GM/Cigna[/TD]
[TD="class: xl63, align: right"]600[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]Value Opt[/TD]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]DTX[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1777[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]Cigna[/TD]
[TD="class: xl63, align: right"]525[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]BCBS[/TD]
[TD="class: xl63, align: right"]478[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]Compsych[/TD]
[TD="class: xl63, align: right"]519[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]GM/Cigna[/TD]
[TD="class: xl63, align: right"]600[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]Value Opt[/TD]
[TD="class: xl63, align: right"]450[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]RTC[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1456[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]Cigna[/TD]
[TD="class: xl63, align: right"]395[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]BCBS[/TD]
[TD="class: xl63, align: right"]264[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]Compsych[/TD]
[TD="class: xl63, align: right"]641[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]GM/Cigna[/TD]
[TD="class: xl63, align: right"]300[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]Value Opt[/TD]
[TD="class: xl63, align: right"]340[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]PHP[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1147[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]Cigna[/TD]
[TD="class: xl63, align: right"]275[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]BCBS[/TD]
[TD="class: xl63, align: right"]123[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]Compsych[/TD]
[TD="class: xl63, align: right"]254[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]GM/Cigna[/TD]
[TD="class: xl63, align: right"]130[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]Value Opt[/TD]
[TD="class: xl63, align: right"]206[/TD]
[/TR]
[TR]
[TD="class: xl64"]LP[/TD]
[TD]IOP[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]760[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]DTX[/TD]
[TD]BCBS CA[/TD]
[TD="class: xl63, align: right"]1275[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]DTX[/TD]
[TD]AETNA[/TD]
[TD="class: xl63, align: right"]1122[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]DTX[/TD]
[TD]Multiplan[/TD]
[TD="class: xl63, align: right"]1292[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]DTX[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1777[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]RTC[/TD]
[TD]BCBS CA[/TD]
[TD="class: xl63, align: right"]1195[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]RTC[/TD]
[TD]AETNA[/TD]
[TD="class: xl63, align: right"]969[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]RTC[/TD]
[TD]Multiplan[/TD]
[TD="class: xl63, align: right"]1105[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]RTC[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1456[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]PHP[/TD]
[TD]BCBS CA[/TD]
[TD="class: xl63, align: right"]550[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]PHP[/TD]
[TD]AETNA[/TD]
[TD="class: xl63, align: right"]195[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]PHP[/TD]
[TD]Multiplan[/TD]
[TD="class: xl63, align: right"]833[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]PHP[/TD]
[TD]OON[/TD]
[TD="class: xl63, align: right"]1147[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]IOP[/TD]
[TD]BCBS CA[/TD]
[TD="class: xl63, align: right"]475[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]IOP[/TD]
[TD]AETNA[/TD]
[TD="class: xl63, align: right"]510[/TD]
[/TR]
[TR]
[TD="class: xl64"]MH[/TD]
[TD]IOP[/TD]
[TD]Multiplan[/TD]
[TD="class: xl63, align: right"]552.5[/TD]
[/TR]
[TR]
[TD="class: xl68"]MH[/TD]
[TD="class: xl69"]IOP[/TD]
[TD="class: xl69"]OON[/TD]
[TD="class: xl70, align: right"]760[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[jimmy90]

What version of Excel are you using?

if you are using 2007+ try a SumIfs

 

[Michael M]

Another option is to put a small selection table off to the side with the same dropdown choices and use a sumproduct

Excel 2003
	A	B	C	D	E	F	G	H	I
1	Facility	LOC	Insurance	Amount		Facility	LOC	Insurance	Amount
2	LP	DTX	Cigna	750		LP	RTC	OON	1456
3	LP	DTX	BCBS	572
4	LP	DTX	Compsych	901
5	LP	DTX	GM/Cigna	600
6	LP	DTX	Value Opt
7	LP	DTX	OON	1777
8	LP	RTC	Cigna	525
9	LP	RTC	BCBS	478
10	LP	RTC	Compsych	519
11	LP	RTC	GM/Cigna	600
12	LP	RTC	Value Opt	450
13	LP	RTC	OON	1456
14	LP	PHP	Cigna	395
15	LP	PHP	BCBS	264
16	LP	PHP	Compsych	641
17	LP	PHP	GM/Cigna	300
18	LP	PHP	Value Opt	340
19	LP	PHP	OON	1147
20	LP	IOP	Cigna	275
21	LP	IOP	BCBS	123
22	LP	IOP	Compsych	254
23	LP	IOP	GM/Cigna	130
24	LP	IOP	Value Opt	206
25	LP	IOP	OON	760
26	MH	DTX	BCBS CA	1275
27	MH	DTX	AETNA	1122
28	MH	DTX	Multiplan	1292
29	MH	DTX	OON	1777
30	MH	RTC	BCBS CA	1195
31	MH	RTC	AETNA	969
32	MH	RTC	Multiplan	1105
33	MH	RTC	OON	1456
34	MH	PHP	BCBS CA	550
35	MH	PHP	AETNA	195
36	MH	PHP	Multiplan	833
37	MH	PHP	OON	1147
38	MH	IOP	BCBS CA	475
39	MH	IOP	AETNA	510
40	MH	IOP	Multiplan	552.5
41	MH	IOP	OON	760
Sheet1

Cell Formulas
Range	Formula
I2	=SUMPRODUCT(--(A2:A41=F2)*(B2:B41=G2)*(C2:C41=H2),D2:D41)

 

[jimmy90]

Another option is to put a small selection table off to the side with the same dropdown choices and use a sumproduct


Excel 2003

	A	B	C	D	E	F	G	H	I
Facility	LOC	Insurance	Amount	Facility	LOC	Insurance	Amount
LP	DTX	Cigna	LP	RTC	OON
LP	DTX	BCBS
LP	DTX	Compsych
LP	DTX	GM/Cigna
LP	DTX	Value Opt
LP	DTX	OON
LP	RTC	Cigna
LP	RTC	BCBS
LP	RTC	Compsych
LP	RTC	GM/Cigna
LP	RTC	Value Opt
LP	RTC	OON
LP	PHP	Cigna
LP	PHP	BCBS
LP	PHP	Compsych
LP	PHP	GM/Cigna
LP	PHP	Value Opt
LP	PHP	OON
LP	IOP	Cigna
LP	IOP	BCBS
LP	IOP	Compsych
LP	IOP	GM/Cigna
LP	IOP	Value Opt
LP	IOP	OON
MH	DTX	BCBS CA
MH	DTX	AETNA
MH	DTX	Multiplan
MH	DTX	OON
MH	RTC	BCBS CA
MH	RTC	AETNA
MH	RTC	Multiplan
MH	RTC	OON
MH	PHP	BCBS CA
MH	PHP	AETNA
MH	PHP	Multiplan
MH	PHP	OON
MH	IOP	BCBS CA
MH	IOP	AETNA
MH	IOP	Multiplan
MH	IOP	OON

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

[TD="align: center"]6[/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"]7[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[TD="align: right"]300[/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"]340[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]I2[/TH]
[TD="align: left"]=SUMPRODUCT(--(A2:A41=F2)*(B2:B41=G2)*(C2:C41=H2),D2:D41)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That would be VERY silly to do such a thing if he has the optino of using SUMIFS


please refer to Excel’s SUMIFS or SUMPRODUCT…Which is faster?

Or try it yourself as I have. I consider sumproduct an array formula (although many will argue the FACT it technically is NOT and i know this), because it performs at the speed of one. And i will always take formulas over arrays, Except in the case of non-volatile functions, although they are faster on the Full calculation of a sheet they show there true colors during recals.

 

[Michael M]

Fair comment.....but it's the best option provided so far....and it may be exactly what the OP wants...