Moving from empty dropdown list to next in order to look up value

[Jarlath]

Hi,

I am currently using the below formula to find the value in my table:

{=IFERROR(INDEX($D$45:$D$404,SMALL(IF($B$19=$A$45:$A$404,ROW($A$45:$B$404)-MIN(ROW($A$45:$A$404))+1,""),ROW(A2))),"")}

A dropdown list is contained in cell b19, there are others in cell b25, and b11.

I need this formula to recognise that if b25 is empty move to b19, if b19 is empty then use b11.

Any help would be greatly appreciated,

Kind Regards

Jarlath

 

[C Moore]

Use the name manager, use_value=IF(NOT(ISBLANK($B$19)),$B$19,IF(NOT(ISBLANK($B$25)),$B$25,$B$11)). I used the not function so it would check in the most likely order, you could reorder it to shorten it by excluding the NOTs. $B$19 in your formula would be replaced with use_value.

 

[Jarlath]

Use the name manager, use_value=IF(NOT(ISBLANK($B$19)),$B$19,IF(NOT(ISBLANK($B$25)),$B$25,$B$11)). I used the not function so it would check in the most likely order, you could reorder it to shorten it by excluding the NOTs. $B$19 in your formula would be replaced with use_value.

Thanks for your help, I can't seem to get this to work.

Would it be possible to lay it out as if B19 wasn't in the original formaula.

So using the name manager method and I want to check b25 first, if blank move to b19 and then if blank move to B11. This is the first (and probably last) time I will use this formula type and I can't quite get my head around it. Thanks for your help

J

 

[C Moore]

original formula:
=IFERROR(INDEX($D$45:$D$404,SMALL(IF(use_value=$A$45:$A$404,ROW($A$45:$B$404)-MIN(ROW($A$45:$A$404))+1,""),ROW(A2))),""), array entered
use_value:
=IF(NOT(ISBLANK($B$25)),$B$25,IF(NOT(ISBLANK($B$19)),$B$19,$B$11))

I interpret what you're doing as finding the 2nd match to one of 3 values in order of B25, B19, & B11, is that correct?

 

[Jarlath]

I changed a few things around and got it to work, I can't thank you enough.

Regards
j