=INDEX(First_field,MATCH(1,(A1:A100=1st_variable)* (B1:B100=2nd_variable)*(C1:C100=3rd_variable)*(D1: D100=4th_variable)*(E1:E100=5th_variable)*(F1:F100 =6th_variable),0))
Not sure if I am interpreting this correctly€¦.
I have 11 fields per record.
Initially I set up criteria and extract ranges €“ works perfectly.
For what ever reasons this has to be done formulaically.
My understanding of using the above array is as follows;
Im setting €ś1st_variable€ť to a cell reference as I have for the 2nd €“ 6th
variables.
€śFirst_Field€ť has been set to the first filed heading result I want returned.
Mock data table:
A, B, C, D
New, Stable, 80 Secure
I pass in A & B wanting to see C
€śFirst Field€ť = C
€ś1st_variable€ť = A
€ś2nd_variable€ť = B
The Array returns a result of 80
I pass in A & B wanting to see C
€śFirst Field€ť = D
€ś1st_variable€ť = A
€ś2nd_variable€ť = B
The Array returns a result of Secure
Im doing something wrong€¦
"Peo Sjoblom" wrote:
One way,
=INDEX(First_field,MATCH(1,(A1:A100=1st_variable)* (B1:B100=2nd_variable)*(C1:C100=3rd_variable)*(D1: D100=4th_variable)*(E1:E100=5th_variable)*(F1:F100 =6th_variable),0))
entered with ctrl + shift & enter, then you change to the second field with
the rest of the formula the same. If the values that you want to return are
numeric you can use sumproduct
=SUMPRODUCT(--(A1:A100=1st_variable),--(B1:B100=2nd_variable),
-- etc,First_field)
Regards,
Peo Sjoblom
"Arturo" wrote:
Table Range A1:K100
Variables get passed to 6 cells out side of table.
I want returned the remaining 5 fields associated with that record.
Filtering is not an option.
I tried concatenating the 6 variables to use VLookup €“ does not work.
What are the formula or function options available to me?
Sincerely,
Arturo
|