Status
Not open for further replies.

#### MARKEGANDERSON

##### Board Regular
Hello all,

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

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!!!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### RalphA

##### Well-known Member
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

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

##### Well-known Member
=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)

Last edited:

#### Smitty

##### MrExcel MVP

It helps everyone stay on the same page.

Status
Not open for further replies.