I have the following code in an excel spreadsheet to search through a list of text (AA5:AA30), from the bottom of the range and then search up, for the exact text (located in C3). When it finds the match It will then post the number from the adjoining list/cell (AC5:AC30). I have dragged this code down 5 rows so as to repeat this process but each time ignoring the last result so there is no repetition. In effect it will create a list of the last 5 results matched.
And it works absolutly fine but...
I have since created a second column of data to search through (AH4:AH30) and return the result from its own adjoining list of numbers (AJ4:AJ30). The problem is that I don't know how to extend the formula to perform the operation through the second set of data.
Help would be gratefully received and many thanks in advance
Iv tried playing around with extending the formula and googling an answer but have no had success.
This is the existing formula
=INDEX(AC$5:AC$30, LARGE(IF(ISNUMBER(MATCH(AA$5:AA$30, $C$3, 0)), MATCH(ROW(AA$5:AA$30), ROW(AA$5:AA$30)), ""), ROWS(I$16:$I16)))
in efffect the new code I want to include will be
INDEX(AJ$5:AJ$30, LARGE(IF(ISNUMBER(MATCH(AH$5:AH$30, $C$3, 0)), MATCH(ROW(AH$5:AH$30), ROW(AH$5:AH$30)), ""), ROWS(I$16:$I16)))
but I am unable to "merge" the two formulas
Bookmarks