Vlookup and lookup

[JV0710]

Good day

Please can anyone help me with this query:

On Sheet 1 of my spreadsheet I have a list of products in rows down and Months in columns across. The months are not fixed and may change as periods roll ( e.g. first quarter to second quarter) - So column B can be "July_2007" now but can change to "October_2007" in a few months time. To help with my Vlookup reference, I have numbered the columns in row 1 ( column A =1, column B = 2 etc ).

On sheet 2 ( Cell E4) , I have a cell that I enter the Month name.

Also on Sheet 2, in ( cell E7 ), I have a vlookup that is looking for the "product" in (D7) in the list on sheet 1, then checking the Month in (E4) on sheet 2, find the column number that the month is in and return the data from that column.

The formula I am using in cell E7 is:
=VLOOKUP(D7,Report1!$A:$AY,LOOKUP($E$4,Report1!$A$5:$S$5,Report1!$A$1:$S$1),0)

The problem i am having is that the lookup function in the formula is only working if (cell E4) is the first month in the list ( cell B5 on sheet 1).

I hope this is making sense.

I would like to know what I am doing wrong or perhaps there is an easier way.

I would appreciate any help

Thank
JVN

 

[jongooligan]

Let me see if I've got this right.

You want to input a date and a product name in sheet 2 and return a cell value from sheet 1 based on that input?

 

[JV0710]

Perhaps another way of asking this question . . .

I would like to do a lookup of some data in a table and return the column number that the data is in.

So, If I have table A1:D6 and in C4 there is my name . . . then i would like to do a lookup of my name in the table and return the column number that my name is in ( in this example - column 3 )

What is the formula to do this ?

 

[JV0710]

Hi Jongooligan

yes, in sheet 2 I want to enter the month ( Say July ) and product and return a cell value based on the input.

Looking up the product is fine because it is always in column A of the table that is being interogated. The month however may not always be in the same column . . . therefore i have to find a way to check the column number of the month.

Thanks

 

[Aladin Akyurek]

=VLOOKUP(D7,Report1!$A$6:$AY$1000,MATCH($E$4,Report1!$A$5:$AY$5,0),0)

Adjust to suit...

 

[JV0710]

Thank you Aladin

Your solution works great

with my formula I was getting results back for some dates and not for others . . .

JVN

 

[Aladin Akyurek]

Thank you Aladin

Your solution works great

You are welcome.

with my formula I was getting results back for some dates and not for others . . .

LOOKUP requires an ascending match-array.

 

[JV0710]

Aladin

Thank you again

I appreciate your help

JVN