IF formula

[Guest]

I've created a drop down list of companies and I want a formula that will
display the relevant details (entered over a number of cells) when I select
one of them. I've been using the below formula which I know is incorrect but
might give you an idea of what im trying to do:

=IF(A2="OMD", C2=Sheet3!D28)

I know you have to enter a value if false aswell but I'm really stuck here.
Also I need to be able to select another value in A2 and have it return
different results.

Help please!

 

[Pete_UK]

You need to use a VLOOKUP function, along the lines of:

=VLOOKUP(A2,Sheet3!A28,4,0)

This assumes that you have a table in Sheet3 occupying cells A2 to D8,
and you want to return data from column D where the data in column A
matches exactly what you have in the drop-down cell.

Hope this helps.

Pete

 

[Guest]

I assume that A2 is where your drop down list is.
In C2, put this formula:
=IF($A$2="OMD",Sheet3!D2,"")
you can now fill that formula on down through C3, C4, C5, C6, C7 and C8.

For the other possibilities (limit is 7 levels of nesting with IF), you add
more IF's to the formula for the not true conditions:
=IF($A$2="OMD",Sheet3!D2,IF($A$2="XYZ",Sheet3!E2,IF($A$2="ABC",Sheet3!F2,"")))

Again you could fill that formula down. The $A$2 makes that address
absolute and it won't change as you fill the formula, and with D2, E2, F2
being relative addresses (no $ signs) they will increase the row number as
they are filled down your sheet.

 

[Bernard Liengme]

A formula can only change what is displayed in its own cell.
Also a cell can contain only one value, not a range such as D28
Not clear what you have for data but based on your example it seems you need
=IF(A2="OMD", Sheet3!D2, "") as the formula in C2
=IF(A2="OMD", Sheet3!D3, "") in C3
To make it easier to copy from C2 to the other cells, start with
=IF($A$2="OMD", Sheet3!D2, "") as the formula in C2
Maybe tell us more about what you want
best wishes

 

[Guest]

Look at Pete UK's proposed solution - upon reflection, he may have offered a
better one if you want the results to just show up in C2, and not a series of
results in several cells on down the sheet.

 

[Guest]

Thanks alot for your help with this. I've copied the formula you provided and
altered it accordingly for each corresponding cell and it works perfectly. If
you could help me with the following, I'll be able to complete what I want.

How do I add more conditions to the formula to provide different results for
another entry in A2 (e.g. changing OMD to M2M)?

 

[Bernard Liengme]

This is a task for VLOOKUP, IF is not suitable here.
On sheet4 in A13 I have
MAT a 1 apple
CAT b 2 pear
DOG c 3 plum


In A1 of Sheet1 I have: CAT
In B1 I have the formula =VLOOKUP($A$1,Sheet4!$A$1:$D$3,2,FALSE) and it
displays: b
In C1 I have =VLOOKUP($A$1,Sheet4!$A$1:$D$3,3,FALSE), it displays: 2
In D1 =VLOOKUP($A$1,Sheet4!$A$1:$D$3,2,FALSE) displays: pear
best wishes