200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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.
=VLOOKUP(ROW()-5,$A$6:$C$10,3,FALSE)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