Adding a text behind the amount in a IF calculation

Occasional Contributor

Hello, I would like some help to add GB (gigabyte) after some of the calculations I have done in a =IF function. For example, in column D5 I have a list with data usage in GB. Looks like 1 GB, 2 GB, 3 GB, 5 GB, 15 GB, 25 GB, 50 GB, 100 GB and Unlimited. What I would like to do is to have a formel saying, for example, =IF(D5=AF3;T3;0). Where D5 is where I want to get the info, AF3 is the list from where D5 has the info and T3 is where I want to compare against. D5 is 1 Gb, AF3 is 1 GB and T3 is 10 GB. The calculation works but I can't get the GB in the calculation. 


Also, I want the calculation to return back "Unlimited" if the GB is more than 60 GB.


Thankful for help

7 Replies

@Jonas44405 Not really sure what you are trying to do, but the formula in the screenshot seems overly complicated. Can you upload the file and mention which Excel version you are using? That would help. 

@Riny_van_Eekelen Ty for your help. I have added the sheet. I am using Office365 so I guess the latest version of excel

@Jonas44405 Thanks, but can you now please explain what need to be displayed and where? The formula in Q16 seems to be just sitting there as an example.

Yes, it's an example so that I can try it before I add it. Q16 formula should be added to F5. What I want is to when change GB in D5, F5 should automatically reflect this. In the example, everything below 10GB and 10GB should be reflected as 10GB in F5. I also would like F5 to have GB after and not only 10 as I have in my example. Add text and numbers. For GB above 50GB I would like F5 to reflect Unlimited (text). Ty

@Jonas44405 Added some extra information in column AG and used XLOOKUP in F5. Is that what you want?

Yes exactly thank you. And this could also be used in H5, J5, etc?
I wanted to ask you if I could add an additional question to this thread? Regarding that, I want to summarize D29 with the value that is in AG4:AG8. D29 is the yearly cost and AG examples are 2,3 years, etc. Ty again