Reputation: 432
I have some testing data that has been run every quarter for several years.
Each table corresponds to one quarter, and has the results for four tests by office. This data is organized in Excel as follows (this is a small sample):
Using this data set, I need to fill out this summary table:
Basically, I need to look up the value in the data set that would correspond to both the row reference and column reference. For example, row reference "UK" and the column reference "Test 1A-Q217", returning "14.85" in the summary table.
I tried using an INDEX( MATCH, ( MATCH)) formula, but that didn't work. Since the tables are laid out one after another, it would just return the results of the first table instead of the reference that matched more closely further down. What would be another way to fill out the table?
Upvotes: 0
Views: 314
Reputation: 106
My ideal way:
Use OFFSET
inside a VLOOKUP
to dynamically set the vlookup area. I would first modify your data set the following ways:
LEFT
/RIGHT
/SEARCH
(see other threads for extracting sub-strings from strings)Your new formula would look something like this
=VLOOKUP($A4,OFFSET($A$3:$G$14,MATCH($V1,$A$3:$A$500,0)-1,),MATCH($V$2,$A$3:$Z$3,0),0)
What is happening is that you are doing normal VLOOKUP
with 2 modifications to make it more dynamic. yous set the lookup table using OFFSET
and you set the column using standard MATCH
approach.
You may need to make additional modifications since I have added a row to your data set.
Note: I have not tested out this formula, you may need to make minor adjustments to the formula but this should get you what you need
Alternatively, you could create named ranges for each of your data sets and have a VLOOKUP
inside your main VLOOKUP
that selects the correct named range to look inside of. I think this is less elegant, will take longer to set up, and and adds additional work every time you want to add another data set each quarter.
Upvotes: 1