Confusion and frustration with complex formula involving ROW, INDEX & SMALL
I need help with where I’m going wrong with this formula. I feel I’m close but I just can’t wrap my head around what I’m doing wrong.
Attached spreadsheet for reference.
2 Sheets.
I’m trying to tell the formula that for every “Invalid” in Column G of Sheet 2, to return the corresponding value in Column A of Sheet 2 of that row. The result should be returned in a data record in Sheet 1.
Please help.
Last edited by JC_LA_1979; 01-28-2014 at 12:33 PM. Reason: Change Title of Thread
Post title changed.
Last edited by ConneXionLost; 01-28-2014 at 01:41 PM.
Would you like to say thanks? Please click the: "Add Reputation" button, on the grey bar below the post.
Have you tried using a VLOOKUP function?
Try this array formula
**Array formulas must be confirmed with Ctrl+Shift+Enter key combination.
=INDEX(Sheet2!$A$2:$A$70,SMALL(IF(Sheet2!$G$2:$G$70="Invalid",ROW(Sheet2!$G$2:$G$70)-1,""),ROW($1:$1)))
you would need to change instances ROW($1:$1))) from 1 to 2 and so on.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Test 4.xlsx
hi,
here attached is the solution I'm proposing.
AL
Test 4.xlsx
SORRY SORRY SORRY there was a little mistake in the file I posted earlier (05:52 PM).
I've just corrected it.
here attached is the correct file.
AL
Ok, so I see what you did, however, in Sheet 2, if I wanted to insert a new row between Rows 3 and 4 with a new record of "Invalid", can I get columns H:J to autopopulate...basically trying to minimize the amount of manual adjustments to a file that will continually change, whether by removing rows or adding them.Originally Posted by AL1976
I'm afraid that with that solution, formulas would need to be dragged in H:J, as in column A...
This may work...
I'm not familiar with roundown - what is your formula saying in English?
@JC_LA_1979
Did you try my formula in post #4?
It works correctly for the 1st instance but when I adjust the Row from 1:1 to 2:2, it gives me a Num error.
Nevermind, I got it working. This may work even better.
Can you update your sample file to show us what results you expect?
Last edited by Tony Valko; 01-28-2014 at 04:31 PM.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Please find attached file with revised formula that that is entered in A4 and dragged down. There no helper columns.
congratulations to Alkey whose solution is definitely better than mine !
You're welcome and please don't forget to thank those who helped by clicking on Add Reputation *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks