IF with INDEX & MATCH

[devo1234]

Hi,

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

 

[RoryA]

Welcome to the forum.

I think you mean:

=IF(N14="RED",INDEX(' table (1)'!I2:S727,MATCH('TEST'!P3&'TEST'!Q3,'table (1)'!G2:G727&'table (1)'!H2:H727,0),MATCH('TEST'!R3,'table (1)'!I1:S1,0)),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))))

 

[devo1234]

Thank you very much! works splendid!