IFS function with sheet link

Copper Contributor

Hi all

In an IFS function I want to have the return values from a different worksheet - but don't know whether this is not possible (no examples show this) or there is a simple novices error in my current attempt (which returns NAME?)

=IFS(G3=“Male Hip”,Sheet3!$R$28,G3=“Male Any”,Sheet3!$P$28,G3=“Female Hip”,Sheet3!$Q$28,G3=“Female Any”,Sheet3!$O$28)

Thanks for help...

13 Replies
Not sure which version of Excel you are using, I use office 2016 on my Mac and the IFS function is not supported (and returns NAME?), you will need to do nested IF statement to get it to work.

Richard
Many thanks Richard, didn't see that stated anywhere, so will try Nested IF
Just checked the Microsoft web site where they have the following note under the IFS description,
Note: This feature is available on Windows or Mac if you have Office 2019, or if you have an Office 365 subscription. If you are an Office 365 subscriber, make sure you have the latest version of Office.

Richard
Thanks I am using my work Office 365 which is 2016, so that's that. My first attempted nested IF doesn't work either
=IF(G3=“Male Hip”,Sheet3!$R$28,IF(G3=“Male Any”,Sheet3!$P$28,IF(G3=“Female Hip”,Sheet3!$Q$28,IF(G3=“Female Any”,Sheet3!$O$28))))

Hi @1orlando 

 

I too had the same problem with your IF statement, ended up redoing it (but looks the same), have attached what I did for your info. I expect someone smarter than us will have an answer.

 

Richard

Again many thanks richard. It won't be difficult to find someone smarter than me!
Just been playing around with your IF statement, Appears that if you retype all of the G3="..." references then the formula works. For some reason it just didn't like that part of the formula.

Richard
Yes tried that and then accepted a suggestion though i couldn't see the difference to what i had ! and it works! many thanks for your effort.
for the record
=IF(G3="Male Hip",Sheet3!$R$28,IF(G3="Male Any",Sheet3!P28,IF(G3="Female Hip",Sheet3!$Q$28,IF(G3="Female Any",Sheet3!O28))))

@1orlando 

As a comment, IFS() shall work on Mac same way as on Windows, i.e. only for Office 365 subscribers or on Excel 2019 for Mac.

 

#NAME! error could be not only since function doesn't exist, but as well if within formula appears curly quote character  instead of straight quote ".

Sergei ..wow i had no idea that the quote characters were different and have never seen this mentioned but now it explains why i couldn't see how the suggestion differed from my now.. many thanks

@1orlando 

Yes, they have different meaning and quite often straight quote is converted into curly one if copy/paste formula from the web. Just be careful with this.

exactly what i had done! merci beaucoup!