Excel Offset Match Match - Help

Hi guys, long time reader - first time poster.

I am having trouble searching & matching in an array in Excel - from what I read, I think OFFSET with nested MATCH's should do the job, I just can't wrap my head around how to do it.

The output table has the following form:

	Date1	Date2
a	X	Y
b	Z	M
c	...	...
d	...	...
e	...	...
f	...	...

So I am looking for X, Y, Z, M etc....using offset match in those cells.

The table that holds the data is where it is tricky. And it comes from another source, so I can't easily manipulate it. It looks as follows:

a	BBG	b	BBG	c	BBG
Date1	X	Date1	Z	Date1	#
Date2	Y	Date2	M	Date2	#
Date3	#	Date3	#	Date3	#

So for each security (a,b,c...) there are two columns...the first that holds the date and the 2nd column has the data (all the #'s) I'm looking to have in the output table as X,Y,Z. There are no blank columns, its always "a" followed by BBG (which is just the heading of the data identifier - it can be blank if not needed) then the next security "b" and so on... Not sure what the easiest way to search through would be.

I hope this is clear; appreciate the help.

It would help a lot if you could attach a sample workbook with a "this is what I got" and a dummied up "this is what I want it to look like."

I am assuming that the Corps are "fixed." I added a helper column to identify the column in which the Corp is found. This could be rolled back into the basic formulas, but it just makes them bigger.

Once I found the column the corp was in, then it was a matter of crafting the Offset command. I made the assumption that there would be no more than 400 rows of data. You can extend this if you wish. I did a match for the date in the range pointed to by the offset command. This gives me the row.

From there it was a matter of using INDEX to dig out the value. I gave a static name to the block of data $B$1:$S$400.

I was off in a couple of places. I had to subtract 4 from my column number to align the table with the physical page and I forgot that match starts counting at 1 and offset starts counting at zero, so I hade to subtract 1 from the date row found by Match.

This fills in your chart on the Output worksheet by using charts produced on Sheet3 that extracts the data for each corporation in one table then extracts the matching dates in a second table. Offset is then used to extract the data that matches the corporation with the dates that you have in row 2 of the Output worksheet.

To extract the corporation names from the Input table this was entered in Sheet3!A2 and filled down
FORMULA 1

Formula:

Please Login or Register  to view this content.

I didn't remove any of the values in the columns that you had in the Output worksheet that were not in the small table.
To extract the corporation values from the Input table this was entered in Sheet3!B2 and filled down and across. This extracts all the values from the Input worksheet.

Formula:

Please Login or Register  to view this content.

A second table was created on Sheet3 that extracted all the dates appropriate to each corporation.
Enter the above FORUMULA 1 in Sheet3!A12 and fill down.
To extract the dates enter this in Sheet3!B12 and fill down and across

Formula:

Please Login or Register  to view this content.

On the Output worksheet B5 enter this formula and fill down and across. This matches the corporation with the date and extracts the value from the tables on Sheet3

Formula:

Please Login or Register  to view this content.

I think I see the issue. It's this formula: =MATCH(B5,BDH!$F$1:$XFD$1,0)

Change BDH!$F$1:$XFD$1 to BDH!$1:$1 ... or ... Change the -4 in the offset formula to -1 (I think - I always have to play around with these things).

I started counting from the first row and made the adjustment between the named data range and the physical sheet columns in the Offset formula. You can start counting from the first column of data in which case the only adjustment you have to make is for the fact that MATCH starts counting at 1 whereas Offset starts counting at zero.

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved.

Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.