Complex nested if formula help...

Closed
iisjo
Posts
1
Registration date
Thursday June 23, 2016
Status
Member
Last seen
June 23, 2016
- Jun 23, 2016 at 04:38 PM
Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
- Jun 24, 2016 at 09:18 AM
Hi, I'm new to this excel lark, i rather like it but am stuck, please can you wise people help me...

heres a pic of what I'm working on...



so using this formula as my basis...

=IF(D15="A",E8,"")

I'm trying to copy text in to the cell from cell E8 depending on the text in D15

the above formula works, but i want to add further layers of complexity, which is where it goes wrong. I tried using a nested if function, i couldn't get it to work.

so heres what I'm trying to do...

In d15 i have options a,b,c or d

the letter in d15 dictates where the text comes from so if its an A thats E8, if its a B then its from E9, C would be E10 etc etc.

How can i have all of these options in one formula in one cell?

Many Thanks

Jo

1 reply

Mazzaropi
Posts
1963
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
April 25, 2022
147
Jun 24, 2016 at 09:18 AM
iisjo, Good morning.

MS Excel is a powerful tool and a lot of times there are many different ways to solve a question.

You asked for an IF Function solution.
Try this one: =IF(D15="A",E8,IF(D15="B",E9,IF(D15="C",E10,IF(D15="D",E11,""))))

Another solutions:

--> Using VLOOKUP Function
=VLOOKUP("JOB " & D15, D8:E11, 2, FALSE)

--> Using MATCH and OFFSET Functions
=OFFSET(E7,MATCH("JOB " & D15, D8:D11, 0), 0)

Please tell us if it worked for you.
I hope I've helped.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0