Clever Geek Handbook

Range-matching index to pull the result in another tab

I have a column in a tab that I am trying to pull data from column A in another tab when it finds a number in B: F

So in one tab I would have 03303 in column D2 and then I try to return it "SECOND" when it finds 03303 in column E. Simple if I do that = INDEX (PG! $ A $ 1: $ A $ 8 , MATCH (D2, PG! $ E $ 1: $ E $ 8.0)), but that won't work for anything else, so I need to search for it in the range B :. R

 FIRST  01234               
 SECOND 03300   03301   03302   03303   03309
 THIRD  03310               
 FOURTH 01597   01598   01599   02850   02851
 FIFTH  00800   00801   00802   00809   
 SIXTH  02600               
 SEVENTH    04245               
 EIGHTH 01020

I've tried searching past questions, but I'm not entirely sure if their questions are similar to mine. Many of them want to sum lines, etc. I just want column A to pull when my column D in another tab matches the number in B: F

+3


source to share


1 answer


Use AGGREGATE instead of MATCH.

=INDEX(PG!A:A,AGGREGATE(15,6,ROW(PG!$B$1:$F$8)/(PG!$B$1:$F$8=D2),1))


enter image description here

+4


source






More articles:


All Articles