Lookup, If, Concatenate Formula, Returning Some Incorrect Values

[Billy Baxter]

Hi,

I have a formula in the pictures below (can't attach file and not able to work out how to do the minisheet upload for 3 sheets) where I look up values on the Risk tab in the Inherent Likelihood and Inherent Impact columns - these return a value from the Data Sheet Tab. - most work ok except: 1. when there is nothing in the likelihood and impact columns there should be a '0' returned as Default. Where there is nothing it is returning a '5' - no clue as to why this is.
2. I get a #N/A for the first value in my data list 'Almost Certain Catastrophic' when all other values are returned without a problem.

All of this above is the same for my Issue tab; there everything is returned ok except for when there is nothing in the 'Impact' and 'Response' columns where there should again be a '0' returned but this time I get a '20' and again no clue as to why.
Again I get a #N/A for the first value in the data list "Catastrophic Convenient" when all other values are returned without a problem.

Any help with this would be much appreciated.

Thanks
Billy

Thanks again for any help
Billy

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

To use LOOKUP your data must be in alphabetical order which it isn't.

 

[Billy Baxter]

Hi Fluff,
Thanks for your reply. I have updated my account details including excel version and platform.
Originally I copied this formula from a friend and it works perfectly fine as it is - I presume it is the Default at the beginning which is the problem but how come it already works 100% in my other file?
You are [obviously] correct, when I move that to its alphabetical position in the data it all works.
Thank you for your help with this, much appreciated.
Regards
Billy

 

[Fluff]

How about

=XLOOKUP(IF(F15&G15="","Default",F15&G15),'Data Sheet'!$A$1:$AD$1,'Data Sheet'!$A$2:$AD$2)

 

[Billy Baxter]

Even better, thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.