Convert MATCH Formula replace zero

Hi,

I would like a formula modified to replace zero with 99
Please refer to sheet of formula and explanation.
Many thanks
IanModify formula -no-zero.xlsx

SpreadsheetsMicrosoft ExcelMicrosoft Office

Last Comment

Ian Bell

8/22/2022 - Mon

zorvek (Kevin Jones)

5/3/2021

=LET(X,INDEX(DFRMDATA!$A2:$KL2,MATCH(F$1,DFRMDATA!$A$1:$KL$1,0)),IF(X=0,99,X))

Kevin

Ryan Chong

5/3/2021

or a simple IF function will do:

=IF( INDEX(DFRMDATA!$A2:$KL2,MATCH(F$1,DFRMDATA!$A$1:$KL$1,0)) = 0, 99,  INDEX(DFRMDATA!$A2:$KL2,MATCH(F$1,DFRMDATA!$A$1:$KL$1,0)) )

Open in new window

ASKER CERTIFIED SOLUTION

Saqib Husain

5/3/2021

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Ian Bell

5/4/2021

ASKER

Thanks guys for your kind contributions.
I decided on yours Saqib as it appears more compact.
perfect!

Ian

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Certified Expert Program

Credly Partnership

Udemy Partnership