IF a value from a list, report one of three options

[tizdall]

Hello!

If I have this list:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[/TR]
[TR]
[TD="align: center"]A3[/TD]
[/TR]
[TR]
[TD="align: center"]A4[/TD]
[/TR]
[TR]
[TD="align: center"]A5[/TD]
[/TR]
[TR]
[TD="align: center"]B1[/TD]
[/TR]
[TR]
[TD="align: center"]B2[/TD]
[/TR]
[TR]
[TD="align: center"]B3[/TD]
[/TR]
[TR]
[TD="align: center"]B4[/TD]
[/TR]
[TR]
[TD="align: center"]B5[/TD]
[/TR]
[TR]
[TD="align: center"]B6[/TD]
[/TR]
[TR]
[TD="align: center"]B7[/TD]
[/TR]
[TR]
[TD="align: center"]B8[/TD]
[/TR]
[TR]
[TD="align: center"]C1[/TD]
[/TR]
[TR]
[TD="align: center"]C2[/TD]
[/TR]
</tbody>[/TABLE]

In another sheet I have a cell where I want to populate 1 of 3 options based on whether the value in an adjacent cell is a part of the range I have defined in the list above.

The 3 options would be "Alpha" "Bravo" and "Charlie"

So, if these are my cells:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]This is the value of the 'adjacent cell' = B7[/TD]
[TD]I need a formula to report "Bravo" because B7 is a part of the "Bravo" range (range defined by me in formula)[/TD]
[/TR]
</tbody>[/TABLE]

If the adjacent cell value would have been 'A1' then the cell to the right should report "Alpha" (same for 'C1' it should report "Charlie" etc)

I am thinking I need a hybrid IF(MATCH( formula but can't seem to find anything that works.


Any help would be appreciated!

Mark

 

[Robert Mika]

Hello!

If I have this list:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A1[/TD]
[/TR]
[TR]
[TD="align: center"]A2[/TD]
[/TR]
[TR]
[TD="align: center"]A3[/TD]
[/TR]
[TR]
[TD="align: center"]A4[/TD]
[/TR]
[TR]
[TD="align: center"]A5[/TD]
[/TR]
[TR]
[TD="align: center"]B1[/TD]
[/TR]
[TR]
[TD="align: center"]B2[/TD]
[/TR]
[TR]
[TD="align: center"]B3[/TD]
[/TR]
[TR]
[TD="align: center"]B4[/TD]
[/TR]
[TR]
[TD="align: center"]B5[/TD]
[/TR]
[TR]
[TD="align: center"]B6[/TD]
[/TR]
[TR]
[TD="align: center"]B7[/TD]
[/TR]
[TR]
[TD="align: center"]B8[/TD]
[/TR]
[TR]
[TD="align: center"]C1[/TD]
[/TR]
[TR]
[TD="align: center"]C2[/TD]
[/TR]
</tbody>[/TABLE]

In another sheet I have a cell where I want to populate 1 of 3 options based on whether the value in an adjacent cell is a part of the range I have defined in the list above.

The 3 options would be "Alpha" "Bravo" and "Charlie"

So, if these are my cells:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]This is the value of the 'adjacent cell' = B7[/TD]
[TD]I need a formula to report "Bravo" because B7 is a part of the "Bravo" range (range defined by me in formula)[/TD]
[/TR]
</tbody>[/TABLE]

If the adjacent cell value would have been 'A1' then the cell to the right should report "Alpha" (same for 'C1' it should report "Charlie" etc)

I am thinking I need a hybrid IF(MATCH( formula but can't seem to find anything that works.


Any help would be appreciated!

Mark

is your value in the cell =B7 which is reference to a cell or= "B7" which is actuall text value.

 

[tizdall]

Text value "B7"

 

[Misca]

If the Alpha, Bravo and Charlie are based on the first letter of the cell value here's one way to do it:

=IFERROR(CHOOSE(COLUMN(INDIRECT(LEFT(INDEX(A1:A15,MATCH(C2,A1:A15,0)),1)&"1")),"Alpha","Bravo","Charlie"),"Missing")

The formula finds the exact match first, then picks up the first digit from the left and tries to come up with a cell address with INDIRECT by combining it with 1. The columns of possible addresses would be 1-3 so the CHOOSE picks up the matching value from the given list. Placed the whole thing with IFERROR just to make it look nicer when the exact match is not found.

Using a lookup list would make the formula much easier to write, understand and modify.

 

[tizdall]

I should have used different examples for what I am going for as the alpha bravo charlie is misleading.

If the text value of a cell in the sheet can be found in range "a" (range defined by me) please report "Alpha"

If the text value of a cell in the sheet can be found in range "b" (range defined by me) please report "Beta"

If the text value of a cell in the sheet can be found in range "c" (range defined by me) please report "Charlie"


That's really all i need it to do, though, the auto reply based off the first digit is very cool! I may use that for a different project.

 

[Robert Mika]

I should have used different examples for what I am going for as the alpha bravo charlie is misleading.

If the text value of a cell in the sheet can be found in range "a" (range defined by me) please report "Alpha"

If the text value of a cell in the sheet can be found in range "b" (range defined by me) please report "Beta"

If the text value of a cell in the sheet can be found in range "c" (range defined by me) please report "Charlie"


That's really all i need it to do, though, the auto reply based off the first digit is very cool! I may use that for a different project.

By range do you mean a Named range anywhere in the worksheet or cells in column(Row 1-10, row 11-20....)?

 

[tizdall]

cells in column, ie i would specify A1:A135 as an example

 

[Robert Mika]

How would you specify a range?
The only way is to use Named Range for each part of your cell in column(row1:20="alpha"....)
But then you can not return the name of the range without using VBA or nested IFERROR statement.

Unless I'm missing something obvious here....