Forum Discussion
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 thinking a VLOOKUP formula would do the trick, but kept running into a problem where the Pending tab returned the data on line 8 eight times until it reached the data on line 9.
Does anyone know a formula that will avoid repeating the same data, and just return each one it finds once?
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.
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.
Thank you Matt! Worked perfectly!!!
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:
