Vlookup on multiple axis with multiple criteria

In the event that you have duplicate scores in the same year for the same person the only way I know to capture

all events is to use an array formula over multiple cells. If you don't have duplicates this still works.

Array enter this formula in B4 of the Calculator sheet. To commit an array formula press Ctrl + Shift + Enter.

Fill down to B7. You will know it has been entered correctly when you see the formula enclosed in { } curly braces.

You don't type those in yourself. Excel does it for you.

Formula:

Please Login or Register  to view this content.

I put duplicate scores into the data sheet to demonstrate. The file is attached.

Hope this helps.

Withdrawn by FR.

Originally Posted by theLOLkid

...... I see that you've used SMALL, IF, ROW, ROWS, and MIN functions but I can't get my head around the algorithm.

Before diving into the SMALL, IF, ROW, ROWS and MIN parts:

I noticed in post #5 you reference the formula
=INDEX(B3:D6,MATCH(A1,$A$3:$A$6,0),MATCH(B1,Data!$B$2:$D$2,0))
with relative cell / range references. In this case B3:D6 must be “anchored” absolutely as $B$3:$D$6 or you will get incorrect results. Perhaps you already knew that but it seemed important I not overlook it.

For the algorithm refer to the formula in my first upload. To help me parse formulas I rely on selective use of the F9 key quite often. While tedious to use I find it pays enormous dividends. It reveals the hidden “insides” of the formula….what each function is passing to the next function or operator. This explanation relies on that.

In the formula bar highlight / select the blue part of the formula in cell B4 Calculator sheet and press the F9 key.
=IFERROR(INDEX(Data!$B$2:$B$5,SMALL(IF(($B$3=INDEX(Data!$C$2:$F$5,0,MATCH(Calculator!$B$2,Data!$C$1:$F$1,0)))*(Calculator!$B$1=Data!$A$2:$A$5),ROW(Data!$A$2:$A$5)-MIN(ROW(Data!$A$2:$A$5))+1),ROWS($1:1))),"")

You should see {1;1;0;0}. These are the results of the TRUEs / FALSEs generated by coercing those text values into their underlying numeric values of 1s and 0s. IF evaluates each of those and every non-zero gets assigned a position specific index number. That is where the if true ROW(Data!$A$2:$A$5)-MIN(ROW(Data!$A$2:$A$5))+1 part comes into play.

If you highlight / select that part of the formula in B4 and press the F9 key you should see {1;2;3;4}. I could just as easily reduced the complication here by replacing the ROW/MIN/ROW part with ROW($1:$4) which I did in the upload post #7. The results are the same. The approach I used is a discipline I’ve learned from this forum. There are a rather large number of array formulas worked out in this forum that can start in any row and end in any row. It assures that the row assignments will always start with 1 and extend to the last row no matter what, helps avoid typos and makes formulas easier to debug. The need becomes more apparent when using named ranges. Here we could get away with ROW($1:$4).

The ROWS($1:1) part is the last argument to the SMALL function that sorts and groups all those resulting assigned index numbers in ascending order. It does this by using the count of the number of rows to designate 1st smallest, 2nd smallest…….. You will notice as you arrow down from B4 to B5 etc. the ROWS values change…..$1:1, $1:2, $1:3 and from the count of the rows assigns each assigned index number its proper place in order. It is these that are then passed to the outermost INDEX function’s first argument …….. INDEX(Data!$B$2:$B$5. Those index number(s) will return the Event(s) in Data!$B$2:$B$5 respective to those row numbers which have been indexed …….. Data!$B$2 being the first row / event in this case.
BTW: I used COLUMNS($A:A) for this same purpose in the second upload because the resulting presentation is column wise.

I hope I haven’t made your eyes “glaze over” with this explanation. If you have any more questions about this formula please let us know.