VlookUp with Multiple Criteria?

[Arturo]

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

 

[Frank Kabel]

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))

 

[Peo Sjoblom]

One way,

=INDEX(First_field,MATCH(1,(A1:A100=1st_variable)*(B1:B100=2nd_variable)*(C1:C100=3rd_variable)*(D1100=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]

=INDEX(First_field,MATCH(1,(A1:A100=1st_variable)*(B1:B100=2nd_variable)*(C1:C100=3rd_variable)*(D1100=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;
I’m 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

I’m doing something wrong…