Geek asks and answers

Horizontal search by multiple criteria

I am trying to develop a formula in excel to search for multiple criteria. In particular, how can I extract value for apples in New York for location (B)?

    Northeast                                        NYC            
     (A)     (B)         (C)     (D)         (A)     (B)    (C)      (D)


Grapes   3,000   2,073   751     2,000       4,253   3,500   1,832   2,500 
Apples   400     3,076   2,298   900         27,250  19,000  14,250  9,000 
Oranges  6.0     3.1     3.9     5.0         28.4    20.0    13.8    10.0
+2
source share
1 answer

I have the following formula to work with your data with one minor problem, you will need to fill in the header columns (Northeast / NYC) to allow the match to work

=VLOOKUP(A9,A1:I5,MATCH((B9&C9),(A1:I1&A2:I2),0),FALSE)

** NB: since the formula uses an array function, you need to execute it using the Crl-Shift-Enter command :)

here is a screenshot to decrypt the links :)

Sheet

I hope this is helpful

+3
source

More articles:


All Articles