CODEWITHSUNDEEP
excelvba
beeba
beeba

Reputation: 432

Finding value from a dataset based on specific header and row

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

enter image description here

Using this data set, I need to fill out this summary table:

enter image description here

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

Answers (1)

Benjamin Goldwater
Benjamin Goldwater

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:

  1. Change the header rows such that col A states the quarter (e.g. "Q1-17") and the other headers just state the Test (e.g. "Test 1A"). This is not a MUST, but will make things significantly easier. If you don't want to mess with your data, you can add a col above/below yur current headers to does this for you using LEFT/RIGHT/SEARCH (see other threads for extracting sub-strings from strings)
  2. In your output table, put test in 1 row (e.g. "Test 1A" in row 1) and date in another (e.g. Q1-17" in row 2)

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

Related Questions