7

I'm trying to create a Risk Assessment sheet in Excel.

I have one drop-down with days at the moment and I want another cell to be populated depending on what day is selected.

I have managed to get this working for one day but not all days.

=IF(F16="Monday","Apple","")

How can I expand this formula to show the following:

F16 = Monday, G16 = Apple
F16 = Tuesday, G16 = Orange
F16 = Wednesday, G16 = Banana
F16 = Thursday, G16 = Pear

7

Basically what you are looking for is called a LOOKUP function in excel.

Step-1: Make a list of X and Y (in your case DAYS and FRUITS) - let us say this range is A1 to B7

Step-2: Write the following formula in the cell where you want the fruit name to appear =VLOOKUP(A8,A1:B7,2,0) (assuming that A8 contains your DAY)

| improve this answer | |
  • This works ok, can it be made to copy the text so it can be edited because at the min if I go to copy the text it just shows the formula? – Ryanagray Aug 25 '15 at 13:20
  • copy the cell with formula and do "Paste-Special" - Now you can edit the text – Prasanna Aug 25 '15 at 13:23
  • Can this be don't programically so it copies the text and not the formula – Ryanagray Aug 25 '15 at 13:27
  • that will involve the use of VBA (read as coding). Would you be interested in going down this lane. I'm assuming your RA is the priority. – Prasanna Aug 25 '15 at 13:30
  • I would be interested in VBA altho I have never done this so could take some explaining. – Ryanagray Aug 25 '15 at 14:15
2

If you don't want to add additional cells just like Prasanna suggested just add:

=IF(F16="Monday","Apple",IF(F16="Tuesday","Orange",IF(F16="Wednesday","Banana",IF(F16="Thursday","Pare",""))))
| improve this answer | |
  • Appreciate the one line formula. But one cannot maintain this, if the number of drop down items are more than what is shown in the example. Mind that @Ryanagray gave an analogy and his/her risk assessment sheet may have more than 7 entries (as shown in his analogy). – Prasanna Aug 25 '15 at 13:16
1

Depending on your version of Excel, you may be able to use the IFS function. This essentially performs the job that the nested IF function would do.

In G18, try:

=IFS(F16=Monday,Apple,F16=Tuesday,Orange,F16=Wednesday,Banana,F16=Thursday,Pear)

| improve this answer | |
  • This isn't in Excel 2013 — what version are you using? – Scott Jan 12 '17 at 5:24
  • 2
    Appears to be available on Excel 2016 onwards only – Kenneth L Jan 12 '17 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.