# Vlookup and lookup

#### JV0710

##### Active Member
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

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

#### jongooligan

##### Board Regular
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

##### Active Member
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

##### Active Member
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

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

#### JV0710

##### Active Member

Your solution works great

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

JVN

##### MrExcel MVP

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

##### Active Member

Thank you again

I appreciate your help

JVN

Replies
11
Views
1K
Replies
3
Views
529
Replies
11
Views
472
Replies
1
Views
351
Replies
3
Views
341

1,191,697
Messages
5,988,163
Members
440,131
Latest member
EricMoz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back