CODEWITHSUNDEEP
excelexcel-formula
jeden
jeden

Reputation: 17

INDEX/MATCH to return matched value in table

I've been having an issue trying to get Excel to return the value in a cell that is immediately to the right of the matched value in a table. I have been trying to use the following formula, but it doesn't appear to like tables, and can't seem to work out how to reference a cell relative to the matched cell (not just a single column reference).

{=IF(ISERROR(INDEX($K$18:$L$42,SMALL(IF($K$18:$K$42=$B$2,ROW($K$18:$K$42)-17),ROW(1:1)),2)),"",INDEX($K$18:$L$42,SMALL(IF($K$18:$K$42=$B$2,ROW($K$18:$K$42)-17),ROW(1:1)),2))}

Appreciate any help/guidance on this!

Upvotes: 0

Views: 105

Answers (1)

user4039065
user4039065

Reputation:

In the following sample image, the formula in I7 is,

=IFERROR(INDEX(Table1[fld B], 
         AGGREGATE(15, 7, (ROW(Table1[fld A])-ROW(Table1[#Headers]))/(Table1[fld A]=H7), COUNTIF(H$7:H7,H7))), 
         TEXT(,))

aggregate_table

Upvotes: 1

Related Questions