none
Use If, Index, Match and Vlookup in single formula. RRS feed

  • Question

  • Hello, 

    I have a set of data, for example 11 columns, that starts with a unique start, ex. "1#&". For each ID there might be 11 columns or some times less. The thing is I want to sort these columns according to the starting of the data in each cell.

    I want a formula that matches the heading, in N1, and so on, with the each cell in the range B2:K12. If the there is match then the content of this cell is copied to its proper location in the right. Example R19 will have the content of B19.

    The formula need to be dragable in both rows and columns.

    Appreciate any help.

    Thank you

    Ahmed

    Monday, July 9, 2018 6:58 AM

Answers

  • In N2:

    =IFERROR(INDEX($B2:$K2,MATCH(N$1&"*",$B2:$K2,0)),"")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ahmed Morsyy Monday, July 9, 2018 8:17 AM
    Monday, July 9, 2018 7:30 AM

All replies

  • In N2:

    =IFERROR(INDEX($B2:$K2,MATCH(N$1&"*",$B2:$K2,0)),"")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ahmed Morsyy Monday, July 9, 2018 8:17 AM
    Monday, July 9, 2018 7:30 AM
  • Hi Hans,

    Thank you very much. Appriciate your very quick reply, you saved my time :)

    Monday, July 9, 2018 8:17 AM