Index with Match?

[rrmack34]

Hello,

I would like to be able to look up the last 3 values in a row that are not zero from another worksheet.

I am using this formula on my current worksheet - {=+INDEX($AN2:$CH2,SMALL(IF($AN2:$CH2>0,COLUMN($AN2:$CH2)-COLUMN($AN2)+1),1))} and it works like a charm.

However when i try to lookup a value from my current worksheet and extract the last 3 non zero results from another worksheet i am having no luck putting a formula together.

So lets say I want to find the last 3 test scores from a student that are located on a different sheet - how would i do that?

Like i said if the text scores were on the same page the above formula works...i just don't know how to combine match or some type of lookup with this formula to get the result i need.

Any help is appreciated.

[bebo021999]

Current formula has no "lookup value". It tried to get the last nth positive value from the right of every row

New requirement ask for "lookup value", I guess it is student ID in current WS to specify which row in other WS, then get the involving data.

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[rrmack34]

thanks for the reply

I do realize there is no lookup value as i specifically indicated that - "i just don't know how to combine match or some type of lookup with this formula to get the result i need"

i need a a formula that looks up the value on another sheet and brings back the first 5 non-zero results.

my first post has the formula for the second part - i just cannot figure out the lookup part...

Attached is a basic spreadsheet example...

[bebo021999]

Try in B7

Please Login or Register  to view this content.

Drag down and accross

[rrmack34]

So i am getting a #NAME? error - however - i am using EXCEL 2007 - so maybe the AGGREGATE command is in the newer editions...

[canapone]

Hi,

Bebo021999's formula for Excel 2007

in B7

=INDEX(Sheet2!$A$2:$L$4,MATCH($A7,Sheet2!$A$2:$A$4,0),SMALL(IF(Sheet2!$A$2:$A$4=$A7,IF(Sheet2!$D$2:$L$4,COLUMN(Sheet2!$D:$L))),COLUMNS($A:A)))

Press F2 to edit the formula, then press CTRL+SHIFT+ENTER to confirm the formula then copy across.

Regards

[bebo021999]

So i am getting a #NAME? error - however - i am using EXCEL 2007 - so maybe the AGGREGATE command is in the newer editions...

Sure. It is strong function but for 2010+ only.
So, follow "INDEX(SMALL ..." construction with array formula.

[rrmack34]

My apologies - after doing the CTRL + SHIFT + ENTER it worked.

I thought i had done it but i guess i had not.

Again - thanks all for your help...

By far the best source of help anywhere.

[rrmack34]

Sorry for the delay in marking solved - was still having issues with the formula - determined that it would not return text - just numbers - so I am good now. Thanks...