If Statement Help

[ScabbyDog]

In range B1:B4 I'll have letters ranging from A to F. So B1 could be E and B2 could be A. These letters will always be unique.

I need an IF statement (I think) that looks for the letter B in this range and if found, returns whatever is in the cell directly to the left of it. If B is not found, then look at the letter E and if found return the cell directly to the left of it. If neither B or E are found then it should look for the letter F (which will be there somewhere) and return the cell directly to the left of it.

[mikeTRON]

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

Please Login or Register  to view this content.

[ScabbyDog]

Thanks Mike but that doesn't work.

I've uploaded a sample.

[FDibbins]

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?

[mikeTRON]

Copy and paste into cell A8:

Please Login or Register  to view this content.

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

[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)

[mikeTRON]

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

[ScabbyDog]

Nope, 4 numbers is as big as it'll get!

I actually got it working using

Please Login or Register  to view this content.

However I need to add something to the code so that if the word the above formula returns already exists in cell A8 then look at the next letter. So if the letter B was found in B1 and A1="Cat" and A8="CAT", then it should look at finding E if it exists in the range and if not look at finding F after that.

[mikeTRON]

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.

[ScabbyDog]

Attached new sample file for you to get an idea of what I'm after. Appreciate the assistance so far.

[ScabbyDog]

Any help with this or could you not figure it out?

[FDibbins]

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

Did you read my post #4?

[mikeTRON]

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

[ScabbyDog]

1. Look for "B"
If found then give the word next to B provided A8 does not contain the same word that is next to B, else go to 2

2. Look for "E"
If found then give the word next to E provided A8 does not contain the same word that is next to E, else go to 3

3. Look for "F"
If found then give the word next to F. A8 will never equal F if the formula gets this far. If not found return "Fail".

[mikeTRON]

And no CAT stipulation?

[ScabbyDog]

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.

[newdoverman]

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.

Please Login or Register  to view this content.

[ScabbyDog]

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.

Please Login or Register  to view this content.

If I put that into A9 and drag across it works but it doesn't take into account what is in cell A8 which is the key to this problem.

[newdoverman]

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.

[ScabbyDog]

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.

You've solved my problem from the initial post. However, I then subsequently added a condition in that has yet to be solved. Hopefully mikeTron or FDibbins can solve it.

[ScabbyDog]

Managed to solve it myself. Was actually much easier than I thought in the end! Just an IF statement with nested AND's in it! Thanks for the help anyway guys.