Cross reference


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.
Who is Participating?
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can use this formula and copy it down and across.


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.
NorieVBA ExpertCommented:
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?
LadkissonAuthor Commented:
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!

The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

Open in new window

These are referenced with the formula:

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).
NorieVBA ExpertCommented:
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.

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.