Need index formula help!

[ntp3326]

Hello,

I have a spreadsheet that keeps track of a number of managers. I have a separate sheet titled 'gaps' that is going to list all stores without an RGM or any store that is a total of 3 managers short of their target. From my research I think I need an index formula to get this done but I am not having luck with coming up with the formula myself.

Any help is appreciated.

Thank you.

[FDibbins]

Hi, welcome to the forum

So, what would some sample answers look like?

[ntp3326]

Hello,

So the goal is that if, on the Guideline sheet, under the RGM column under the Actual header, there is a value of 0
then the corresponding store number and the AC will appear on the list of missing RGM in the Gap sheet.

Similarly if, on the Guideline sheet, under the Overall column under the Variance header,
there is a value of -3 or less, then the corresponding store number and AC will appear on the list of missing managers in the gap sheet.

I'm not sure if image uploads are working, but if they are:
As an example, if this was the data input on the guideline sheet
Capture.PNG

Then this is what the output on the gaps sheet would be
Capture2.PNG


Hopefully this clears up some confusion on what I am trying to do!

Thanks for any help.

[Glenn Kennedy]

Please - not a picture of a sheet... an actual sheet...

[AliGW]

Glenn - see post #1.

[Glenn Kennedy]

Dohh!!

In A3, copied across to B3 and down:

=IFERROR(INDEX(Guideline!B:B,SMALL(IF(Guideline!$N$3:$N$12<0,ROW(Guideline!$N$3:$N$12)),ROWS(A$3:A3))),"")

with a similar formula in F&G.

Array Formulae are a little different from ordinary formulae 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 brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use 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 brackets yourself - it won't work...

Answers are not the same as per you picture, becasue the raw dat are different.

[ntp3326]

Glenn you rock!

This worked perfectly. Thank you so much!
Doubt I ever would have gotten it fully figured out on my own.

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.