Solved

Cross reference

Posted on 2014-01-13
6
230 Views
Last Modified: 2014-01-15
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
Comment
Question by:Ladkisson
6 Comments
 
LVL 33

Expert Comment

by:Norie
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

by:Ladkisson
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

by:Angelp1ay
ID: 39777690
I put the cell ranges into B5:M6 using this formula:
=ADDRESS(2,7+52*(COLUMN()-2))

Open in new window


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

Open in new window


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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 33

Expert Comment

by:Norie
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

by:Angelp1ay
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)
...

Open in new window

0
 
LVL 43

Accepted Solution

by:
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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

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

Join & Ask a Question