Solved

# Cross reference

Posted on 2014-01-13
250 Views
Hi!

column A lists numbers in a certain order that can't be changed. the same numbers but in a different order are listed across the row 2 (G2 - XQ2) I need populate the area (B8 - M62) by matching the numbers and referencing the corresponding values located in G3 - XQ3.
Please refer to the attached file.
thanks!
Cross-reference-Formula.xlsx
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions

LVL 34

Expert Comment

ID: 39777157
What's to be used to determine which group of values the result should be taken from?

For example in B9 we would be looking for 85, but from which group of numbers in row 3?
0

Author Comment

ID: 39777344
All of them. Please follow my example in cells B8 - M8. So for example, the first section G3 - BF 3 should populate column B or cells B8 - B62; the next section BH3 - DG 3 should populate column C or cells C8 - C62 and so on. Hope this helps!

thanks
0

LVL 11

Expert Comment

ID: 39777690
I put the cell ranges into B5:M6 using this formula:

These are referenced with the formula:
=HLOOKUP(\$A8,INDIRECT(B\$5):INDIRECT(B\$6),2,FALSE)

It takes ages to calculate on my computer though so I haven't copy pasted everywhere. It's the same formula copied to all cells though (it takes into account changes in rows/columns appropriately).
Cross-reference-Formula-solved.xlsx
0

LVL 34

Expert Comment

ID: 39777704
What do you mean 'all of them'? All of what?

Perhaps you can use the formula I posted for you previous question, with a minor adjustment.

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

LVL 11

Expert Comment

ID: 39777705
You can probably speed up the processing by hardcoding instead of using indirects
...but then you need a new formula for each column...

e.g:
Col1   =HLOOKUP(\$A8,\$G\$2:\$BF\$3,2,FALSE)
Col2   =HLOOKUP(\$A8,\$BG\$2:\$DF\$3,2,FALSE)
Col3   =HLOOKUP(\$A8,\$DG\$2:\$FF\$3,2,FALSE)
...
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 450 total points
ID: 39778713
You can use this formula and copy it down and across.

=INDEX(\$G\$3:\$XQ\$3,1,(COLUMN()-2)*53+MATCH(\$A8,\$G\$2:\$BF\$2,0))

Your spreadsheet, however is overloaded with the tons of pictures which is hampering the performance severely. You will get much better performance if you get rid of the pictures.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
move line without macro or copy/paste 6 45
Pull data from a another spreadsheet tab 3 30
copy down array 24 34
Unique List in UserForm 3 23
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month5 days, 16 hours left to enroll

#### 734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.