How to do a Vlookup without knowing the column number

I have a spreadsheet that I currently use to track the total revenue for a variety of different products. The data is pulled from a Pivot on a different sheet that is updated daily. I currently use VLookup to pull this information by matching the ItemID (Column A).

Here is my problem: I need to add the monthly revenue for each month after a product is released to this spreadsheet. Unfortunately, each product has a different release date so I can't tell Vlookup an exact column number to search for the corresponding data.

Is there a way I can do a conditional statement or something similar instead of giving an exact column number in Vlookup? For example, have it look for the first column that contains data and return that as Month 1, and so on for month 2, 3, etc. If not, can you think of any other way to set this up? Also, please note that some months may have 0 revenue, which will need to be included as a value, not skipped over.

I have attached a sample Workbook that is modeled in a similar way to the actual spreadsheet I am using, except MUCH less data and using a static table for the source data instead of a dynamic Pivot table. Does anyone have any idea how I can populate the month revenue from the 'Source Data' sheet? Please note that the actual spreadsheet I am using has the Pivot table refreshed daily with updated information and there are new products added all the time so the information never stays in the same column or row #.

P.S. On the 'Source Data' sheet you will notice that the Year and Month headers are on separate rows, unfortunately I can't alter this (DB is read-only) to have them combined on 1 line (e.g. Dec. 2012, Jan. 2013, etc), which means I can't look the column up by matching the Release Date to the corresponding Month & Year.

Try this in the Sales Tab E2 and filled right/down

=INDEX('Source Data'!D$3:D$12,MATCH($A2,'Source Data'!$A$3:$A$12,0))

No, that returned the values in column D (Dec. 2012) for Month 1. Dec. 2012 shouldn't be Month 1 for all of the items, Month 1 will be different depending on when the item released. Essentially, I need Month 1 revenue to be the first column on each item's row that contains a value.

For example:

Item 1: Mo. 1 rev = 342.00
Item 2: Mo. 1 rev = 260.00
Item 3: Mo. 1 rev = 322.00
and so on..

Does that make sense?

Originally Posted by Jonmo1

Try this in the Sales Tab E2 and filled right/down

=INDEX('Source Data'!D$3:D$12,MATCH($A2,'Source Data'!$A$3:$A$12,0))

Hi tigeravatar-

When I attempted to place this in my spreadsheet the formula wouldn't work for all of the cells because I have many years worth of data. For example, the month of January exists multiple times from 2006-2013 so the formula only wants to return the first instance of this value, which is 2006 and it uses Jan. 2006 that for every month of January after that.

I revised that spreadsheet to include more than one years worth of data and changed the release dates. If you look at the values for Item 1 in Months 11-13, the values don't match the Source Data because the formula appears to be looking for values from 2012 so instead of Month 13 having data from March 2013, it shows data from March 2012. I attached the revised spreadsheet to this thread.

Is there any way you can think of around this? That's why I didn't try to match the release date month to the column headers, because the way it's set up, the month and year are on separate lines.

Originally Posted by tigeravatar

funroe,

Attached is a modified version of your posted sample workbook.
In sheet 'Sales' cell E2 and copied over and down is this formula:

Please Login or Register  to view this content.

Does that work for you?

Hi FDibbins-

I can't change the column headings in the Source Data as this is from a read-only Database and even if I change it now, when I refresh the query, it will overwrite my changes.

Originally Posted by FDibbins

If you change your headings to real dates...1/1/13, 2/1/13 etc, then you can use this, copied down and across...

=INDEX('Source Data'!$A$2:$I$12,MATCH($A2&$B2&$C2,'Source Data'!$A$2:$A$12&'Source Data'!$B$2:$B$12&'Source Data'!$C$2:$C$12,0),MATCH(E$1,'Source Data'!$A$2:$I$2,0))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

I think that worked!! Thank you so much for your help. I am going to keep messing with this. I have a quick question: what if I wanted to make this cumulative? Is there a way to make it so it adds the value of the previous cell into the current month when calculating it? I'm thinking of eventually creating benchmarks for 1month, 6months, 9months, 12months and 24months.


Thanks again for all of your help!!

Originally Posted by ChemistB

Must be an easier way but this works in E2 copied down and across. It must be entered as an array formula (using CNTRL SHFT ENTER)

=IFERROR(INDEX('Source Data'!$D$3:$H$12,MATCH($A2,'Source Data'!$A$3:$A$12,0), MATCH(1, 1/(ISNUMBER(INDEX('Source Data'!$D$3:$H$12, MATCH($A2, 'Source Data'!$A$3:$A$12,0),))),0)+COLUMN(A1)-1),"")

See attachment