Using Index Match to find the second matched column in a region

Question

I tried to use Index/Match function to lookup values from certain region, because I was stuck since my column index may have two same value, for example you have a region looks like below

For example I want to find the value for 1/24/2018 for Test 1, but I use the formula, it gives me the first value for Test 1 which is 0.31, but what I want to is 0.83

=INDEX(G5:I11,MATCH(F5,F5:F11,0),MATCH("Test 1",G4:I4,0))

I did some search, I think I should use large, so which means give the location of column for the last match value you find in the row, not the first, but I am not sure how to implement Large function to my formula

Which means give me the column number for last match you find in second match, you know when you put MATCH("Test 1",G4:I4,0)), it returns the first matched value, but I want to 2nd or 3rd (which means last)

  Test 1   Many  Test 1 
1  0.31    0.21   0.83
2   1       2      3

Where do you get the value 0.31 from? And as there is no value of 0.83, where do you think that will come from? — Solar Mike, May 13 '19 at 06:11
@SolarMike can you see the new data I edit? I don't know what happened, my image which I updated is now shown — Worst SQL Noob, May 13 '19 at 06:30
@SolarMike 0.31. If you use index match to this table, the match the row number for "1" and match the column number for "Test 1", but I want the second location of the "Test 1", not the first — Worst SQL Noob, May 13 '19 at 06:42
I know how index() and match() work. I asked you which of the tables of data you are using, as the original data you showed did not match your results you stated or expected to get. — Solar Mike, May 13 '19 at 07:00
To deal with duplicate values, have a look at https://stackoverflow.com/a/56016036/4961700 — Solar Mike, May 13 '19 at 07:36

score 1 · Accepted Answer · answered May 13 '19 at 07:58

You can use INDEX() on just column I in your case. That way you can loose the reference to a column.

Below I included an example, plus if you must use LARGE().

Formula in B11:

=INDEX(D2:D8,MATCH(B10,A2:A8,0))

Formula in B12:

{=INDEX(B2:D8,MATCH(B10,A2:A8,0),LARGE((B1:D1="Test1")*(COLUMN(B1:D1)-1),1))}

Notice the last one is an array formula entered through CtrlShiftEnter

score 1 · Answer 2 · answered May 13 '19 at 08:17

A possible solution based on your question, though your sample data is a bit hard to understand, might be this formula in B11. ARRAY Formula: SHIFT + CTRL + ENTER

=INDEX($B$2:$D$7,
        MATCH($A11,$A$2:$A$7,0),
        SMALL(IF($B$1:$D$1=B$10,COLUMN($A$1:$C$1)),COUNTIF($B$10:B$10,B$10))
      )

You can drag it sideways. Be careful with the $, when you adapt the formula to your own table.

As you already said, you need SMALL in the formula. The IF function looks for occurences of, for example, Test1 in the upper table and gives back the column numbers. The COUNTIF counts the occurences of the search term up to the current column and feeds the result to SMALL.

Using Index Match to find the second matched column in a region

2 Answers2