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

 

[Fluff]

You've got brackets in the wrong places, try
=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))))))

 

[willow1985]

Excel changed the formula to: =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))))))

However it is still not returning the correct values.
For example for the first set it is returning C3 from Sheet 2 which is correct as A3 on Sheet 2 matches D23 on Sheet1. However for the 2nd IF function it returns E4 when A3 and D23 match and for the 3rd IF function it returns "0" (G5-11 are blank in Sheet 2)
Basically the index is not staying lined up on the same row with the first match C-A, E-A, G-A and so forth.

I am not sure if I am explaining this properly.

Thank you

Carla

 

[Fluff]

That formula works fine for me, so without being able to see your data, I've no idea why its returning the wrong value.

 

[willow1985]

IF C14=SPM,Metco or Honeywell AND IF D23= 444 or any of the below Values in Column A

EXAMPLE

Sheet 1

C14: Metro
D23: 601

FORMULA:[Formula should return Bond: n/a for SPM, 1000psi for Metco and 2350psi for Honeywell] = 1000psi

Sheet 2

Column A Column B Column C Column D Column E Column F Column G

Powder SPM Metco Honeywell Hardness Bond Hardness Bond Hardness Bond 444 - 2500psi - >4000psi - 4250 psi 601 - n/a - 1000psi - 2350 psi <colgroup><col><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[willow1985]

Sorry it did not line up nicely but hope this helps

 

[willow1985]

Nevermind. I found my error.

Thank you very much for your help!

 

[Fluff]

That's exactly what I get. Do you have any merged cells?

 

[willow1985]

I do have merged cells but it is working now so all is good. One more question, where would I add an IFERROR return blank in this formula?
basically starting the formula with IFERROR( and ending with ," ").

Thank you

Carla

 

[Fluff]

Like
=IFERROR(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))))),"")