# Cross reference in excel

Hello!

column A has numbers that are arranged in certain way and can not be changed.
Cells F3 - XQ3 have data that needs to be linked with the data from column A.
I manually populated cells B8 - M8 to show what needs to be done.
It is very time consuming to do it cell by cell. I am wondering if there is a formula that can help me expedite these calculations.
thanks!
Cross-reference-Formula.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Analyst Assistant Commented:
Try this in A8 an copy across/down.

=INDEX(OFFSET(\$A\$2,1,(CODE(B\$6)-CODE("A"))*53+6,1,52),,MATCH(\$A8,OFFSET(\$A\$2,,(CODE(B\$6)-CODE("A"))*53+6,1,52),0))

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
AWESOME!!! Thank you!!
Developer AnalystCommented:
Here is it using a VBA function.

Public Function CrossRef01(rngVal As Range) As Long

Dim arrXrefRange(12) As String

arrXrefRange(0) = "G2:BF2"  'A
arrXrefRange(1) = "BH2:DG2" 'B
arrXrefRange(2) = "DI2:FH2" 'C
arrXrefRange(3) = "FJ2:HI2" 'D
arrXrefRange(4) = "HK2:JJ2" 'E
arrXrefRange(5) = "JL2:LK2" 'F
arrXrefRange(6) = "LM2:NL2" 'G
arrXrefRange(7) = "NN2:PM2" 'H
arrXrefRange(8) = "PO2:RN2" 'I
arrXrefRange(9) = "RP2:TO2" 'J
arrXrefRange(10) = "TQ2:VP2" 'K
arrXrefRange(11) = "VR2:XQ2" 'L

'Search the range
For Each cell In Range(arrXrefRange(rngVal.Column - 2))
If Range("A" & rngVal.Row).Value = cell.Value Then
CrossRef01 = Sheet1.Cells(cell.Row + 1, cell.Column).Value
Exit For
End If
Next

End Function
Cross-reference-Formula.xlsm
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.