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 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.

      • RoryTodd's avatar
        RoryTodd
        Copper Contributor
        Sep 22, 2020

        SergeiBaklan 

        Hi Sergei,

        Thanks so much for your help and advice - I came right with XLOOKUP

        Works perfectly!!

Resources