CODEWITHSUNDEEP
excelexcel-formula
Worst SQL Noob
Worst SQL Noob

Reputation: 189

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

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

enter image description here

=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

Upvotes: 0

Views: 139

Answers (2)

OverflowStacker
OverflowStacker

Reputation: 1348

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))
      )

enter image description here

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.

Upvotes: 1

JvdV
JvdV

Reputation: 75840

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().

enter image description here

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

Upvotes: 1

Related Questions