Reverse Formula

[adam]

Hi anyone,

I have a workbook with two sheets where one is source sheet and the other is the destination sheet. I have formulas in my destination sheet where I collect data from the source sheet according to the format I need. The formulas work fine and does the needful.

My question is how can I edit or modify the formula so that when I place the formulas in A2,B2, And C2 the data from the source sheet gets automatically filled instead of having to write the formulas in each row.

Also how could I remove the square brackets from the column headers when they get copied.

Any help would be kindly appreciated.

I've attached the workbook for refence.

Note: my data in the source sheet will be increasing as the user adds data to the source sheet.

[HansV]

Which version of Excel do you need to support?

[adam]

Excel 2019

[HansV]

Excel in Microsoft 365 has new functions that do what you want, but those functions are not available in Office 2019.
But you can expand the ranges in the formulas so that they will still work when data are added.
See the attached version.

ReverseLookupWhenNumberInTable.xlsx

[adam]

Thank you for the help Hans.

In that case, can my recommendation be done in googlesheets?

I tried copying the initial formula. It gave me parse error.

Can the formula be changed into an array formula in google sheet for the three columns?

[HansV]

I have no experience with Google Sheets, sorry.

[hamster]

You can try Power Query

s666-PQ-ReverseLookupWhenNumberInTable.xlsx

[snb]

In VBA:

Code: Select all

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)
  
  For j = 0 To UBound(sp) - 1
   x = j \ (UBound(sn, 2) - 1) + 2
   y = j Mod (UBound(sn, 2) - 1) + 2
   sp(j, 0) = sn(x, 1)
   sp(j, 1) = Replace(Mid(sn(1, y), 2), "]", "")
   sp(j, 2) = sn(x, y)
  Next
  
  Sheet1.Cells(1, 8).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

[adam]

Late reply. Was away. But thank you all for the help. Much appreciated.