IF/INDEX/MATCH Formula

[tropics123]

Hi guys, I'm running into an issue which I'm hoping someone might have a solution. For some reason, the INDEX/MATCH formula will not pull in the correct result if it's in %. For example, the IF/INDEX/MATCH formula on Sheet 1 is pulling info from Sheet 2 and the answers could be: Not Applicable, In Service, Out of Date, 100%. The issue is, the result isn't returning the correct answer when it's in %.

Example, Cell B2 on Sheet 1 is looking for Lewis' "Client Info", which is 100% and the returned result shows 1. My formula is also stating that if the answer is "100%", then show the answer as "100% Ready for Processing". However, since the result returns as "1" instead of 100%, the IF formula isn't giving the answer of "100% Ready for Processing."

Please help!

Sheet 2

Name	Client Info
Bob	Not Applicable
Jenny	In Service
Mary	Out of Date
Lewis	100%

 

[Joe4]

That is because 100% is actually equal to 1! If you just format the column with the results as a Percentage, it will show you 100%.

In mathematical terms,
100% = 1
90% = .9
75% = .75
etc

 

[Snakehips]

If you do not have any other Client Info that will return as 1 then just edit your IF statement to like...

IF(???? = 1 then "100% Ready for processing", ??????)

 

[Joe4]

All you really need to do is format the cell/column where the values are being returned to a Percentage.
As all the other options are text strings, formatting the cells as a Percentage will have no impact of those.

 

[tropics123]

If you do not have any other Client Info that will return as 1 then just edit your IF statement to like...

IF(???? = 1 then "100% Ready for processing", ??????)

Hi @Snakehips I had tried that and it didn't work.

 

[tropics123]

All you really need to do is format the cell/column where the values are being returned to a Percentage.
As all the other options are text strings, formatting the cells as a Percentage will have no impact of those.

Hi @Joe4 I had tried to change the format to percentage and unfortunately, it didn't work.

 

[Joe4]

Hi @Joe4 I had tried to change the format to percentage and unfortunately, it didn't work.

Then I don't think you did it correctly.
Go to the cell that is returning the 1 and do the following:
1. Right-click on the cell
2. Select "Format Cells"
3. Go to the Number tab
4. Select "Percentage" and set Decimals to 0
5. Click "OK"

Is that still does not work, please post your formula, as I suspect something may be going on to change the value to text.

 

[tropics123]

Then I don't think you did it correctly.
Go to the cell that is returning the 1 and do the following:
1. Right-click on the cell
2. Select "Format Cells"
3. Go to the Number tab
4. Select "Percentage" and set Decimals to 0
5. Click "OK"

Is that still does not work, please post your formula, as I suspect something may be going on to change the value to text.

@Joe4 Here is my formula. If the result is 100%, then show "100% Ready for Processing", if not then return whatever that's in column B for Lewis or whichever name is entered in cell B2 on Sheet1.

Current Formula:
=IFERROR(IF(INDEX(Sheet2!$1:$1048576,MATCH('Sheet1'!$B$2,INDEX(Sheet2!$1:$1048576,,MATCH("Name",Sheet2!$1:$1,0)),0),MATCH("Client Info",Sheet2!$1:$1,0))="100%","100% Ready for Processing",INDEX(Sheet2!$1:$1048576,MATCH(Sheet1!$B$2,INDEX(Sheet2!$1:$1048576,,MATCH("Name",Sheet2!$1:$1,0)),0),MATCH("Client Info",Sheet2!$1:$1,0))),"")

 

[Joe4]

The issue is you are returning a number, but appear to be checking for a text entry of 100% (anything enclosed in quotes, like "100%" is treated as literal text).

Try changing the ="100%" part of your formula to 100% or 1 (with no double quotes).

 

[tropics123]

The issue is you are returning a number, but appear to be checking for a text entry of 100% (anything enclosed in quotes, like "100%" is treated as literal text).

Try changing the ="100%" part of your formula to 100% or 1 (with no double quotes).

@Joe4 Ah, that fixed it! Thank you!