Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How to index and match multiple criteria?

for example:


a b c

1 x 1 20

2 x 2 21

3 x 3 22

4 y 1 23

5 y 2 24

6 y 3 25


i need when i input x and 1, to index 20

when i input y and 2, to index 24

i used index and match but i can only match one criteria, what about if i want to match 2 criteria??

i used =INDEX(c,MATCH(cell1,a))

how to match (cell1,a) and (cell2,b) for example?

please help i need a solution urgently

Posted on Sep 6, 2013 5:47 AM

Reply
7 replies

Sep 6, 2013 12:17 PM in response to rami.sayegh

Hi Rami,


I'm asuming that "i need when i input x and 1, to index 20" means that when you enter "x" in one cell and "1" in another, the formula is to return the value in C3 (shown as 20 in the example, but could be text in the general case.


One way to do it is to create a single column containing an unique identifier for each row, then constructing a search value to match from the two separate inputs.


In the Lookup table (your sample table above), that will require either replacing columns a and b with a single column containing the combined values, or adding a column where the combined value is constructed from the values i the two columns. The second method is shown below.


In the example below, I've also added a second column to the lookup table to demonstrate that this method will also work with text values in the results column (c).

User uploaded file

Lookup 2 is your sample table. x and y entries are in column B, 123 entries in column C.

Column D (labeled 'ab') contains the formula below, which concatenates the contents of cells in columns B and C into a singe, unique text string.


Formula in Lookup 2::D2, and filled down: =B&C


Column E (labeled 'c') contains the values to be returned, taken from your sample table.

Column F contains an alternate set of text values as a demonstration that this method may be used with either numeric or text values.


In the table on the left, named 'Main,' the two part code is entered into columns B and C (labeled 'a' and 'b' respectively). The formula in column D (labeled 'c') combines the two parts into a single search value, then looks up that value in the first column (D) of the actual lookup table (columns D and E of Lookup 2), and returns the associated value from column E:


Formula in Main::D2, and filled down:


D2, and filled down: =VLOOKUP(B&C,Lookup 2 :: $D:$E,2,0)


Column E of Main contains a formula with three revisions:


The lookup table has bee enlarged to include a third column (F)

The value returned comes from the third column of the lookup table (3)

The VLOOKUP formula has been enclosed in an IF statement to replace the spurious zeroes that occur where they occur in the version above with a fixed text value ("-") and suppresses calculation of the 'correct' value until entries of both parts of the code have been made.


E2, and filled down: =IF(AND(LEN(B)>0,LEN(C)>0),VLOOKUP(B&C,Lookup 2 :: $D:$F,3,0),"-")


Regards,

Barry

Sep 6, 2013 8:29 PM in response to rami.sayegh

HI Rami,


The syntax for INDEX, found in the iWork Formulas and Functions User Guide does support the use of two index values, and each of these could be determined using a Match statemen:


INDEX(range, row-index, column-index, area-index)


But note that the first index is a row index, while the (optional) second one is a column index. For these to both be useful, the data array to which INDEX is to be applied must be a two-dimensional array, not a single dimension column (or row).


MATCH works with a single dimension range of cells, and returns the position of the match-value within that row or column. Apple states that fairly clearly in the article on MATCH in the F&F guide previoulsy mentioned.


Although Microsoft's description of the MATCH function does not state that MATCH is restricted to a single dimension array, all of the examples given use an array consisting of a single column of vaues. I suspect it works in the same manner as MATCH in Numbers (with one notable exception: Excel appears to required an ordered list of values, ordered to match the type of MATCH specified. Numbers does not require the list to be sorted.


If you rearrange your data into a rectangular grid, and do the same with the set of xyz and 123 values used for the search. it would be possible to use the two search values directy to return the right value.


It would be messay though, as I recall from a similar question earlier.


Regards,

Barry

How to index and match multiple criteria?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.