Forum Discussion

Jusdin Rendahl's avatar
Jusdin Rendahl
Copper Contributor
Jul 06, 2018
Solved

VLOOKUP help

Hello!  I'm trying to write a formula that will recognize a line of data based "Pending" or "Resolved" in the STATUS column, and place that line in the appropriate tab (see attached image).  I was th...
Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    Jul 06, 2018

    Hey Jusdin-

     

    Hope you're doing well.  You'll need an array formula to accomplish your task.  Try something like this:

     

    =IFERROR(INDEX(Main!$A$2:$G$7, SMALL(IF(Main!$A$2:$G$7="Resolved", MATCH(ROW(Main!$A$2:$G$7), ROW(Main!$A$2:$G$7)), ""), ROWS(A2:$A$2)), COLUMNS($A$1:A1)),"")

     

    Confirm using CTRL + SHIFT + ENTER

     

    I've also attached an example .xlsx file for your reference.

     

Jusdin Rendahl's avatar
Jusdin Rendahl
Copper Contributor
Jul 09, 2018

Thank you Matt!  Worked perfectly!!!  

Matt Mickle's avatar
Matt Mickle
Bronze Contributor
Jul 09, 2018

Glad you were able to get it working!  Array formulas are quite useful in certain situations.  Here's a link to some Guidelines and examples:

 

https://support.office.com/en-us/article/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7