lundi 18 juin 2018

IF with INDEX & MATCH

I need to tell my formula to search for one specific value in two different tabs. I currently wrote this (and it's working):

=INDEX(' table (2)'!I2:S727,MATCH('TEST'!P3&'TEST'!Q3,'table (2)'!G2:G727&'table (2)'!H2:H727,0),MATCH('TEST'!R3,'table (2)'!I1:S1,0))

I have two conditions: BLUE and RED.

The above formula is searching for a specific value using BLUE as condition in table (2). I then added:

=IF(N14="BLUE",INDEX(' table (2)'!I2:S727,MATCH('TEST'!P3&'TEST'!Q3,'table (2)'!G2:G727&'table (2)'!H2:H727,0),MATCH('TEST'!R3,'table (2)'!I1:S1,0)))

Works fine.

I would like to make the formula knows that if I want the colour BLUE then search table (2), if I want red then search table (1).

My current spreadsheet has 3 tabs: TEST = where my workings are table (1) where RED data is table (2) where BLUE data is

Thanks for your help

Aucun commentaire:

Enregistrer un commentaire