Unresolved question...help please!!!!!!

[MARKEGANDERSON]

Hello all,

I am in desperate need on finding a formula that works....

Please see the below link...

http://www.mrexcel.com/forum/showthread.php?t=350701

So far this is the closest I got, but it only pulls the values for 01 Nov.....

=INDEX(NS_Tally!W1082:W1360,MATCH($J$32,NS_Tally!V1082:V1360,0))

Helllllppp!!!

 

[RalphA]

Mark:
When you merge two or more cells, you have instructed Excel to consier that group of cells as one, namely, the first one in the merged group.
In your case,wWhen you tell Excel to find something in a cell which is the first in a group, it will relate to ghe respective cell just below it, namely, W1082; Since the merged group is V1082:V1090, Excel now sees the range V1082:V1090 as consisting only of one cell, V1082, and the next cell in column V will be V1091.
Aladin Akyurek told you to UNMERGE the group of merged cells FIRST. Did you do that? If you did, and you think it doesn't work for you, you will have to post the values in the merged cells, V1082:V1091, in the cells W1082:W1091, and in J32. Then, someone will be able to work out your problem.

 

[MARKEGANDERSON]

Thanks Ralph!

I did unmerged all the cells....

Cell V1082:1090 are now all Nov 1st

Cells W1082:W1090 has diff values...

Now when I change j32 to Nov 2nd, it does not pick up the values for that group...

It only works for Nov 1st for some strange reason...

hellllppp!!!

 

[RalphA]

I copied your original formula,
=LOOKUP($J$32,NS_Tally!V1082:V1360,NS_Tally!W1082:w1360)
to an Excel spreadsheet, to cell X1082. I noticed two things that may be wrong.

1. the third part of your formula should be the number 2, for "column 2" of the range you are using in the second part, "NS_Tally!V1082:V1360". That is what I see in my Excel 97 as being required.

2. Using just LOOKUP didn't work, for me. But, using VLOOKUP did.

So, doing those changes without even un-merging the range V1082:V1088. Of course, only the value in cell V1082 can be picked up, as, if merged, VLOOKUP jumps from V1082 to V1089, and, if unmerged, with just blanks in V1083:V1088, it does the same. And, if you fill the blancks with the date, 1-Nov-2008, it will pick up the first occurrence of that date in cell V1082 only.

Please use the modified formula below, and report back:
=VLOOKUP($J$32,NS_Tally!V1082:V1360,2)

 

[Smitty]

Please continue in your original post.

It helps everyone stay on the same page.