Vlookup, Index or Match - Problem

[prkhan56]

Hello All,
I am using Office 2003 and have the following problem:

I have a Sheet PROD (figures for example purpose only- it can be
numeric or alpha numeric)
Col B Col H
1 Mar-07 123
2 xx xx
3 xx xx
4 Mar-07 345
5 xx
6 Mar-07 678
7 Apr-07 1234
8 Apr-07 5678
9 xx xx
10 Apr-07 9101
....
....
....

Below there are other months also...Col B will go down for a complete
year. i.e. probably 500 Rows down.
I have a Sheet name REPORTS where I wish to have the information from
Sheet PROD when I input the Month in Cell B6 on Sheet REPORTS for
example = Mar-07
The Formula should look up the contents of Cell B6 on REPORTS and
display the information from PROD Sheet as shown below in Cell A40
(downwards)

123 I want to input the Formula and drag it down in Cell A40 (on
REPORTS)
345
678

For various reasons I cannot sort Col B of PROD Sheet. I tried Vlookup
but it returns only 1 value.
I searched the groups for Vlookup, Index & Match etc but could not see
any thing which suits my need.

Any one can help me

TIA

Rashid Khan

 

[Bob Umlas]

If I understand, put 3/1/07 in B6 of Reports sheet, then put this in cell
B40:
=MATCH($B$6,OFFSET(PROD!$A$1,B39,0,1000,1),0)+B39
(B39 is blank)
Fill this formula down as far as you want.
in C40, enter:
=INDEX(PROD!H:H,B40)
and fill this down as far as you want. You will what you're looking for.

Bob Umlas
Excel MVP

 

[prkhan56]

If I understand, put 3/1/07 in B6 of Reports sheet, then put this in cell
B40:
=MATCH($B$6,OFFSET(PROD!$A$1,B39,0,1000,1),0)+B39
(B39 is blank)
Fill this formula down as far as you want.
in C40, enter:
=INDEX(PROD!H:H,B40)
and fill this down as far as you want. You will what you're looking for.

Bob Umlas
Excel MVP











- Show quoted text -

Thanks Bob.
The formula works fine. However, there are 2 problems
1) B40: =MATCH($B$6,OFFSET(PROD!$A$1,B39,0,1000,1),0)+B39 shows
Jan-00 for Mar-07 and Feb-00 for Apr-07 and so on so forth.

2) C40 : Displays #NA where there is no data available. Similarly #NA
is shown in B40 where is no data

Can you suggest solutions for these two problems.
Thanks once again
Rashid Khan