I would like to take a list like this:

original table

and convert it to this, where the same values line up by column for each row record:

end result


One way is to just populate the grid with a SEARCH formula.

Spreadsheet view

I have added column headers which are used in the formula to determine the matches.


Put this formula into cell C3 and drag it over and down.

SEARCH will return the location, counting from the left, of the contents of C$1 within the string in cell $B2. SEARCH is not case-sensitive, so if you want a to be not equivalent to A, then use FIND instead.

Both SEARCH and FIND will return errors if not found, so the IFERROR captures that and returns "" instead.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.