Converting Formula Yes No 0 1

[Mstg007]

.
I am trying to combine the first formula with the one below. The It will read from the index a yes or no, and I want the yes or no to be converted to 0 and or 1.
Thanks for any help!

=(INDEX('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0)))

=if(A1=0,"NO",if(a1=1,"YES",""))

 

[Mstg007]

my head my be hurting... here is the original formula you gave that works...

=(INDEX('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0))="No")+4

not quite sure

 

[Rick Rothstein]

my head my be hurting... here is the original formula you gave that works...

=(INDEX('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0))="No")+4

not quite sure

Just wondering if you saw my suggestion in Message #9... as long as there are only three choices, it seems implementing it would simplify things greatly for you.

 

[Mstg007]

.=MATCH(index,{"R","Yes","No"},0)+2

=MATCH(INDEX('Rational Method Storm Sewer'!$AS$20:$AS$100,MATCH($BK20,'Rational Method Storm Sewer'!$A
$20:$A$100,0)),{"R","Yes","No"},0)+2

=CHOOSE(A1-2,"R","Yes","No")

=CHOOSE(MATCH('Sheet2'!$AS$20:$AS$100,MATCH($BK20,'Sheet2'!$A$20:$A$100,0)),(A1-2,"R","Yes","No")

 

[Mstg007]

Wow.... Easy. Nice! and thank you!

 

[Mstg007]

Can I get this to reverse? [=3]"R";[=4]"Yes";"No"

 

[Rick Rothstein]

Can I get this to reverse? [=3]"R";[=4]"Yes";"No"

No, the format only works with numbers... but I am wondering why you would need to. The idea behind my suggestion was that you would do all your Excel work using the same numbers as the other program you mentioned that only worked with the number, but just using the Custom Format that I suggested to show the numbers to you as the letter/words you wanted to see.

 

[Mstg007]

Because the way we use values from the program then reimport back into our spreadsheet to give our final results. I hope that clears that up.

 

[Jonmo1]

Can I get this to reverse? [=3]"R";[=4]"Yes";"No"

From the earlier post..

To reverse it
=CHOOSE(A1-2,"R","Yes","No")
A1 is the cell holding the 3 4 or 5

So if A1 holds the number 3 4 or 5
That formula will convert it to R Yes or No

 

[Rick Rothstein]

Because the way we use values from the program then reimport back into our spreadsheet to give our final results. I hope that clears that up.

In that case, I do not think you will be able to effectively use what I had suggested... go with what Jonmo1 has been suggesting to you.

 

[Rick Rothstein]

To reverse it
=CHOOSE(A1-2,"R","Yes","No")
A1 is the cell holding the 3 4 or 5

No "real" saving here, I guess, but you can eliminate Excel from having to do the subtraction (A1-2) by just putting in place-holder commas for index 1 and 2...

=CHOOSE(A1,,,"R","Yes","No")