If Formula - Error when I extend Range

[tlc53]

Hi there,

I have a basic formula =IF(D181=B268,B181,)
This correctly returns "Dairy Foods"
However, when I try to extend the range =IF(D181:D182=B268,B181:B182,)
It returns #VALUE !, instead of "Dairy Foods".

Does anyone know why it's going that?

Thank you!

 

[DanteAmor]

D181:D182 This is not a correct way.
What do you need to do?
You can explain it with real examples, what do you have in the cells and what do you expect from the result?

 

[tlc53]

Column D has a Dynamic list which the drop down options are No.1 through to No.10 (non repeating)

I have a section below for No.1
To feed the information through to here, I want an if statement which says if a cell in Column D has No.1, return the description of Column B.

In this example, No.1 has been selected under the description "Dairy Foods". If I just refer to singular cells it calculates correctly but I need it to look at all of Column D and return the corresponding description of Column B.

The full if statement I want but doesn't seem to work is, =IF(D151:D236=B239,B151:B236,"")
This would return "Dairy Foods"
(slight change in cell references from original posting as I'm currently working on the spreadsheet and deleting rows etc).

 

[DanteAmor]

Try:

=IFERROR(OFFSET(B151,MATCH(B239,D151:D236,0)-1,0),"There is no coincidence")

 

[tlc53]

Very clever! Thanks so much

 

[DanteAmor]

Youre welcome & thanks for the feedback