VLOOKUP no longer sorting groups? What changed?

1. VLOOKUP no longer sorting groups? What changed?

Hi All,

I have attached a workbook here, but this post is only interested in Worksheets 1 ("Tracker") and 2 "("Pipeline).

Basically, "Pipeline" pulls ID#s into one of the 4 colored boxes by their group status ("Pending" for the first group, "Consented" for the next group, etc).

You can see what that looks like in Pipeline! here:
16-282 Study Tracker and Accrual 2016.08.17.xlsm

However, something happened (literally no idea) while I was working, and next thing I know - Pipeline! is drawing blanks:
16-282 Study Tracker and Accrual 2016.08.23.xlsm

Can anyone help me figure out why the function stopped working? I don't believe I've changed any reference rows/columns.

Alternately, I could ask if someone could just help me figure out a new formula that would work.

Thank you in advance to the many talented people on here.

-AW

2. Re: VLOOKUP no longer sorting groups? What changed?

There's a whole pile of stuff here. WHERE EXACTLY are the formulae that are causing you problems?

3. Re: VLOOKUP no longer sorting groups? What changed?

Hi Glenn,

The formulae are the ones located in the Pipeline! worksheet, cells A3, B3, C3.

4. Re: VLOOKUP no longer sorting groups? What changed?

Take a look at the ranges in A3. They are not the same. Two stop at 102 and ROW stops at 103. Fix that and your formula works again. Maybe you should consider using dynamic named ranges instead of fixed ranges. It'll prevent that sort of problem from happening again.

5. Re: VLOOKUP no longer sorting groups? What changed?

Hi Glenn,

Good note about dynamic ranged vs fixed ones - I know that I had problems with that angle previously so I stuck with this when it worked.

I went back to the ranges you mentioned, and I have corrected it as such (copied from cell A3 from Pipeline!):

=IFERROR(INDEX(Tracker!\$A\$3:\$A\$103,SMALL(IF(Tracker!\$F\$3:\$F\$103=Pipeline!A\$1,ROW(\$A\$3:\$A\$103)-ROW(\$A\$3)+1,""),ROWS(\$A\$3:A3))),"")

I'm still drawing blanks. The key is that A3 is not pulling the code from Tracker!

Thank you sir

6. Re: VLOOKUP no longer sorting groups? What changed?

It works OK here. See attached file. Did you remember to enter it as an array formula?

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

7. Re: VLOOKUP no longer sorting groups? What changed?

Totally nailed it. CTRL + SHIFT + ENTER it is.

Thank you so much for catching that. I had completely forgotten about the array function entry.

Glenn +10

8. Re: VLOOKUP no longer sorting groups? What changed?

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1