IF with INDEX MATCH

[rmn123]

Hi!

I am trying to create an IF formula with two INDEX MATCH formals within it. ie. if a cell says one thing use INDEX MATCH A otherwise use INDEX MATCH B.

This is the formula I have written and it is still not pulling correctly.

=IF(BH7="#N/A",(INDEX('[File A]Sheet1'!$G:$G,MATCH(D7,'[File A]Sheet1'!$F:$F,0))),(INDEX('[File B]Sheet1!$A:$A,MATCH(D7,'[File B]Sheet1!$G:$G,0))))

Any advice would be greatly appreciated.

Thanks!

 

[Marcílio_Lobão]

rmn123, Good afternoon.

Try to use:

=IF(ISERROR(BH7),(INDEX('[File A]Sheet1'!$G:$G,MATCH(D7,'[File A]Sheet1'!$F:$F,0))),(INDEX('[File B]Sheet1!$A:$A,MATCH(D7,'[File B]Sheet1!$G:$G,0))))

Please, tell us if it is what you want?
I hope it helps.

 

[philwojo]

Are you typing the #N/A into cell BH7? Or is there a formula in that cell that could have that output? If it is a formula even though #N/A is displayed I don't think it is actually text you can test for.

 

[offthelip]

It is difficult to help you with out more details, but the way I approach these sort of problems is to copy the equation into notebook (or any text editor) and then copy the equation back to three or four spare cells, the reason for doing this is if yoiu copy with excel, excel will automatically change all the realtive addresses, copying via note book means you get cells which are identical.
I then get rid of different bits of the equation and check the each bit is getting the correct answer. eg. test the index, match bits separately and test the kif statement on its own