Forum Discussion
Excel formula help
I am trying to figure out a formula that would allow me to create a result like this...
In Column P, I would like it to automatically generate a text based on numbers entered in column N.
For example..
If N2=1 then P2 would automatically enter “Diamond”
If N2=2 then P2 would automatically enter “Mounting”
If N2=3 then P2 would Automatically enter “Gemstone”
I need to be able to apply these formulas to the entire column for both column N and Column P.
Anyone up for the challenge?
How do I do this?
In Column P, I would like it to automatically generate a text based on numbers entered in column N.
For example..
If N2=1 then P2 would automatically enter “Diamond”
If N2=2 then P2 would automatically enter “Mounting”
If N2=3 then P2 would Automatically enter “Gemstone”
I need to be able to apply these formulas to the entire column for both column N and Column P.
Anyone up for the challenge?
How do I do this?
Variants for P2 could be
=IFERROR(CHOOSE($N2,"Diamond","Mounting","Gemstone"),"")
or
=IFNA(LOOKUP($N2,{1,2,3,3.01},{"Diamond","Mounting","Gemstone",""}),"")or
=IFERROR(IF($N2<1,NA(),INDEX({"Diamond";"Mounting";"Gemstone"},$N2)),"")copied down till end of the range. Text constants could be substituted by cells range
- I am not sure how you are familar with EXCEL.
I suggest you create the following in a sub sheet
columnA
1
2
3
columnB
Diamond
Mounting
Gemstone
in your data sheet, columnP use vlookup function to find the value according to columnN. When you are using VLOOKUP, be aware of the difference between Ralative Row/Column and Absolute Reference
VLOOKUP function
https://support.office.com/en-gb/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
