IF-INDEX-MATCH: Multiple IF functions with multiple matches returning various results

[willow1985]

URGENT: I am having a problem with the below formula and seem to be having an off day and can't figure this out. The formula will match the first criteria but not the second and 3rd IF functions. It will just return "False" for the other 2. Any idea what I am missing?

=IF(C14="SPM",(INDEX(Sheet2!C:C,MATCH(D23,Sheet2!A:A,FALSE),IF(C14="Metco",(INDEX(Sheet2!E:E,MATCH(D23,Sheet2!A:A,FALSE)),IF(C14="Honeywell",(INDEX(Sheet2!G:G,MATCH(D23,Sheet2!A:A,FALSE)))))))))

If I type in SPM in C14 it will return the value in C:C that corresponds with A:A however I get a "FALSE" for the remaining 2.

Thank you for your help

Carla

 

[willow1985]

Changed the Sheet name to Data.

=IFERROR(IF(C14=Data!B1,INDEX(Data!C:C,MATCH(D23,Data!A:A,FALSE)),IF(C14=Data!D1,INDEX(Data!E:E,MATCH(D23,Data!A:A,FALSE)),IF(C14=Data!F1,INDEX(Data!G:G,MATCH(D23,Data!A:A,FALSE)),""))),"")

 

[Fluff]

Does this do what you want
=IFERROR(INDEX(Sheet2!B3:G4,MATCH(D23,Sheet2!A3:A4,0),MATCH(C14,Sheet2!B1:F1,0)+1),"")

 

[willow1985]

No. This formula does not work. I also need to match the columns not specific ranges incase they need to add more data.

=IFERROR(IF(C14=Data!B1,INDEX(Data!C:C,MATCH(D23,Data!A:A,FALSE)),IF(C14=Data!D1,INDEX(Data!E:E,MATCH(D23,Data!A:A,FALSE )),IF(C14=Data!F1,INDEX(Data!G:G,MATCH(D23,Data!A:A,FALSE)),""))),"")

Data!B1 contains text: SPM
Data!D1 contains text: Metco
Data!F1 contains text: Honeywell

Cell C14 contains a drop down list of these manufacturers so when one is selected and there is a match between C14 and Data!B1 etc I want it to perform the proper index Match.

if C14=DataB!
then Match D23 wiith Data!A:A and return the value from Data!C:C.

 

[willow1985]

I got it to work. was an error in the drop down. the formula works as:

=IFERROR(IF(C14=Sheet2!B1,INDEX(Sheet2!C:C,MATCH(D23,Sheet2!A:A,FALSE)),IF(C14=Sheet2!D1,INDEX(Sheet2!E:E,MATCH(D23,Sheet2!A:A,FALSE)),IF(C14=Sheet2!F1,INDEX(Sheet2!G:G,MATCH(D23,Sheet2!A:A,FALSE)),""))),"")

Thank you for everything and for sticking with me!

Carla

 

[Fluff]

Glad you got it working & thanks for the feedback