Forum Discussion

BriannaYK's avatar
BriannaYK
Copper Contributor
Nov 16, 2020

Help pulling row information from a list using IF

I'm trying to pull information from a row in a separate sheet using if to see if it meets the criteria within a column. e.g. if a cell in column A = 3, I then want it to show the information from col...
Excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Nov 16, 2020

BriannaYK 

IMHO, everything works correctly in your case. Function return array of values, if condition is FALSE the value in array is equal to zero, if TRUE - to some number. 

If you are on subscription model, you shall be on Excel with Dynamic Arrays which returns entire such array. The answer will be 0, 0, 4500, 0, etc

Pre-DA Excel will return only first element of such array, e.g. zero. And that's what formula dialogue shows.

 

If you'd like to return element from third position of the result using the condition TRUE, tat will be another formula. Which one it's better to discuss on your sample.

  • BriannaYK's avatar
    BriannaYK
    Copper Contributor
    Nov 16, 2020

    Thanks for your help SergeiBaklan. Now I understand and yes, I would like only the element with the result using the condition TRUE to be displayed. What formula should I be using for this?

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Nov 17, 2020

      BriannaYK 

      Formulas could be different, also depends on your version of Excel. This one shall work in any case

      =INDEX('202022'!$K$2:$K$36,MATCH(140807,'202022'!$D$2:$D$36,0))

Better keep lookup value in the cell, let say A1
=INDEX('202022'!$K$2:$K$36,MATCH(A1,'202022'!$D$2:$D$36,0))

       

Resources