Forum Discussion

RoryTodd's avatar
RoryTodd
Copper Contributor
Sep 10, 2020

Converting data for multiple sequential numbers sheets

Hi everyone, I was wondering if anyone can help?! I have a quote register sheet in excel with several information fields with a quote number that runs sequentially - I also have a job number sheet ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 10, 2020

RoryTodd 

You may use XLOOKUP or similar function, but that's better to illustrate on concrete sample.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 11, 2020

    RoryTodd 

    I did that for the client name, not sure what else shall be taken

    Formula is

    =IFNA(INDEX(
  'QUOTE REG'!$F$15:INDEX('QUOTE REG'!$F$15:$F$100,COUNTA('QUOTE REG'!$D$15:'QUOTE REG'!$D$100)),
  MATCH($B9,'QUOTE REG'!$D$15:INDEX('QUOTE REG'!$D$15:'QUOTE REG'!$D$100,COUNTA('QUOTE REG'!$D$15:'QUOTE REG'!$D$100)),0
  )
),"")
    • RoryTodd's avatar
      RoryTodd
      Copper Contributor
      Sep 12, 2020

      SergeiBaklan 

      Hi Sergei - Great - thank you very much - I did manage to watch a few youtube examples of using Xlookup and have got the basics right but will try your formula and see how that works.

      Will let you know - thanks again for the formula

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 12, 2020

        RoryTodd 

        Sure, XLOOKUP will be easier if it is available in your version  of Excel. Couple of notes

        - I'd recommend to use dynamic range as in formula above not to change formulas from time to time if static range is expanded

        - if column headers will be the same in both sheets you may generate more universal formula MATCH not only rows but columns as well. Thus it'll be same formula for all cases.

Resources