Index match across tables

[kerstin boelsen]

Hi

I'm trying to create a index match query that matches the employee ID to another table .
The question is "in table 2, which new product (1-5) is mapped as true for each employee ID? return the true value as '1', False value = '0'
i tried this, but failed impressively

if( index match [position ID, postion id column, ] , match NEW_product 1, table 2[#header], 0 ) , 1, 0)

if (employee ID in table 1) is present in Table 2, match the New product cell with a valueTRUE and return '1' in Table 1 in column product change.


is that possible or do I need to go via several formulae?

 

[pecoflyer]

Please post a sample sheet ( no pics please).Thx

 

[ETAF]

if you change the title headers to match
try

=IF(INDEX($K$3:$O$7,MATCH($A3,$J$3:$J$7,0),MATCH(B$2,$K$2:$O$2,0)),1,0)

you will get N/A error if id not found

 

[bosco_yip]

Try,

In B3, formula copied across right and down:

=IFERROR(0+INDEX($K$3:$O$7,MATCH($A3,$J$3:$J$7,0),MATCH("new "&B$2,$K$2:$O$2,0)),"")

 

[ETAF]

clever - made a note
=IFERROR(0+INDEX($K$3:$O$7,MATCH($A3,$J$3:$J$7,0),MATCH("new "&B$2,$K$2:$O$2,0)),"")

 

[kerstin boelsen]

clever - made a note
=IFERROR(0+INDEX($K$3:$O$7,MATCH($A3,$J$3:$J$7,0),MATCH("new "&B$2,$K$2:$O$2,0)),"")

this worked like a charm. thank you SO SO much!

 

[ETAF]

Hi, it ws not my formula - but bosco_yip