If Statement Help

I think a sample workbook would help but give this a try.

=IFERROR(IFERROR(IFERROR(MID(B1,FIND("B",B1)-1,1),MID(B1,FIND("E",B1)-1,1)),MID(B1,FIND("F",B1)-1,1)),"")

THis is obviously a very simplistic example, and, with 6 possible letters, could probably be done with index and 6 nested iferror/matches, but I have a feeling you have far more than 6 - which makes that options impractical.

What exactly are you trying to do here, and can you give a more realistic example?

Copy and paste into cell A8:

=IFERROR(INDEX(A$1:A$4,MATCH("B",B$1:B$4,0)),IFERROR(INDEX(A$1:A$4,MATCH("E",B$1:B$4,0)),IFERROR(INDEX(A$1:A$4,MATCH("F",B$1:B$4,0)),"")))

Then you can copy and paste that into G8, L8 and P8.

Mike I was thinking along those same likes (you can just use 1 index, and put the IFERRORS all inside that)


=INDEX(A$1:A$4,IFERROR(MATCH("B",B$1:B$4,0),IFERROR(MATCH("E",B$1:B$4,0),IFERROR(MATCH("F",B$1:B$4,0)))),"")
(Think I got teh ) right)

However, if you start adding more, it will soon become unmanageable (either way)

Originally Posted by FDibbins

Mike I was thinking along those same likes (you can just use 1 index, and put the IFERRORS all inside that)


=INDEX(A$1:A$4,IFERROR(MATCH("B",B$1:B$4,0),IFERROR(MATCH("E",B$1:B$4,0),IFERROR(MATCH("F",B$1:B$4,0)))),"")
(Think I got teh ) right)

However, if you start adding more, it will soon become unmanageable (either way)

Yeah I originally started your method as I personally like it more, but I think someone who is newer will more easily follow the formula I put together... but who knows haha

Provide an example workbook that is representative of your problem and we can help otherwise we are just spinning our wheels talking in the forum.

Provide plenty of test cases to prove the logic works, like your new found CAT exemption.

Sorry, if anything, that is even more confusing that the 1st sample file

Did you read my post #4?

Yeah I read it but I am not sure I follow as well.

Are you telling me that if CAT exists AT ALL, then it should ALWAYS return cat, otherwise return the name next to B, then E then F?
Or are you saying if B exists then use it, if B doesnt exist THEN look for CAT and if so then CAT otherwise look for E THEN F?


EDIT: Actually, yeah your example is completely different than your first example. Make a CLEAR example and explain the logic that needs to happen maybe like the example I gave below.

1) look for B
if B appears then give word next to B, if not go to 2)
2) look for CAT
3) look for E
4) look for F

ETC ETC ETC

And no CAT stipulation?

CAT is just a random word. Whatever is contained left of where B or E or F is found and within cell A8 is what matters. I used CAT as an example of what could be in cell A8 or in the range A1:A4.

This I think is similar to Ford's solution. Enter in A8, and for the other amend the ranges as required. You could fill this across then delete the formulae that are between the ranges that are not needed.

Formula:

=IFERROR(IFERROR(IFERROR(INDEX(A1:A4,MATCH("B",B1:B4,0)),INDEX(A1:A4,MATCH("E",B1:B4,0))),INDEX(A1:A4,MATCH("F",B1:B4,0))),"Value not found")

In light of your description of the problem at the beginning, A8 has nothing to do with it. When you stated the problem, you gave a specific order for matches to occur but your last statement doesn't make sense when compared to the statement of the problem.