show cell value depending on what is in matched E:E

[nzt101]

Hi guys,

In a workbook i have pages Data and Sheet1, in Sheet1!E:E i will sometimes have the text N/A
the below code goes into a cell and shows the result, instead of showing N/A is there a way of making the result say NONE instead?

the concat and randbetween combines a prefix that is needed when the E:E contains something other than N/A, (which currently works perfectly as is)
but if the E:E has N/A i'd like it to just say NONE instead of "prefix N/A"
(the below also allows me to type in a manual option into E19 which i need to stay)

=CONCAT(INDEX(Data!E2:E7,RANDBETWEEN(1,ROWS(Data!E2:E7))))&IF(E19<>"",E19,LET(m,MATCH(C3,Sheet1!D:D,0),d,INDEX(Sheet1!E:E,m),IF(d<>"",d)))

I'm not sure if the above is possible, but thanks for any help

 

[awoohaw]

Maybe wrap the above in IFERROR( .... formula... , "NONE")

 

[nzt101]

Hey awoohaw,

I tried that, but it gave me exactly the same result as the current formula does, because theres no error in the formula or data to make it show NONE

 

[awoohaw]

Hey awoohaw,

I tried that, but it gave me exactly the same result as the current formula does, because theres no error in the formula or data to make it show NONE

please use the xl2bb add in and put a small sample of your data and the formula so it can be used.

 

[awoohaw]

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com