VlookUp with Multiple Criteria?
 

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

Hi
try something like the following array formula (entered with
CTRL+SHIFT+ENTER)
=INDEX(K1:K100,MATCH(1,(A1:A100="value1")*(B1:B100 ="value2)*...*(F1:F100="value6"),0))

--
Regards
Frank Kabel
Frankfurt, Germany
"Arturo" schrieb im Newsbeitrag
...
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

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

=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