I have attached a spreadsheet

I need a formula to give the gears for the items in column C

  I tried to use "index match" formula - using a wild card in column A   =INDEX($B$6:$B$10,MATCH("*"&A6&"*",$C$6:$C$10,0)) BUT THIS NOES NOT WORK

Update to variatus answer

I tried to create helper column - but not right yet updated sheet is attached




The good news is that your formula works perfectly. The bad news is that it works on the wrong logic. The formula is looking for "SPRINT" in column C, finds it in the second row and continues to return the number of gears from the second row.

What should be done is to look for "PARK VALUE" in column A, find it in the 5th row and return the number of gears from the 5th row.

The problem won't be overcome without a helper column. You might hide it. The overtly easier way should be to write the name found in column A in a helper column that translates column C.

A more adventurous solution might try to use the MATCH function from your formula to feed into a helper column translating column A. That would write the number 1 into the helper column next to "VALUE" creating the correct association. Presuming that the helper column is column A the formula below would extract the number of gears.


ROW()-5 would, in fact, number the rows 6:10 as 1 to 5, and that are, of course, precisely the numbers you have in the helper column.


variatus thanks for the help

I tried to create a helper function, but not right yet


Answer the Question

You must create an account to use the forum. Create an Account or Login