MS Excel help

**boybi** · November 20th, 2014 12:15 PM

I have an excel workbook with several worksheets. In one of the worksheets, I have the following format:

-----A------B------C-----D------E
1 XXXX BBBB CCCC DDDD EEEE
2 Jan 1 Jan 2 Jan 3 Jan 4 Jan 5
3
4
5

What formula should I use to get the value in row 1 corresponding to a particular date in row 2?

**jut703** · November 20th, 2014 12:27 PM

Index Match pwede.

=index(A1:E1, match(reference cell, A2:E2))

When you type Jan 3 in your reference cell, it'll return CCCC. Pwede rin siya baliktad just switch the range of the index and match arrays (i.e. type BBBB tapos yung result is Jan 2).

**boybi** · November 20th, 2014 03:16 PM

Thank you! It worked!

**african888** · November 27th, 2014 08:28 PM

pwede rin ba index sa vertical list?

**jut703** · November 27th, 2014 09:23 PM

Originally Posted by african888

pwede rin ba index sa vertical list?

Yup. Just select the correct arrays

**Yuki13** · June 19th, 2015 07:07 PM

Help guys.

Trying to drag down this formula ='Deal 01'!C6

Result I need is this ='Deal 02'!C6

But when I drag I'm getting this ='Deal 01'!C7

**Yuki13** · June 19th, 2015 07:29 PM

Originally Posted by Yuki13

Help guys.

Trying to drag down this formula ='Deal 01'!C6

Result I need is this ='Deal 02'!C6

But when I drag I'm getting this ='Deal 01'!C7

Up! I have 1850 cells to fill in!

**jut703** · June 19th, 2015 08:34 PM

Your dragging really just advances cells, not reference sheets.

Here's what I would do:

1. Make a separate column that has Deal 01 in the first row. Drag it down such that the next rows will be Deal 02, Deal 03, and so on until Deal 1850.

2. On the adjacent cell (if string Deal 01 is in cell A1, then type in B1), use the formula, =INDIRECT("'"&A1&"'!C7")

Just tried it and it works. The text "Value in Deal 01 C7" is what I put in cell C7 of sheet Deal 01 and it was able to reference it properly:

imageuploadedbytsikot-forums1434713561.350356.jpg

**Yuki13** · June 22nd, 2015 10:56 AM

^thanks jut. appreciate the effort, i vaguely remember you posting that you came from a long drive last Friday.

I took an easier route, adding columns would have been a pain. I failed to mention that I already had 74 columns to begin with. Wanted to drag down into 25 rows, each with different reference sheets.

Here's what I did:

1. Locked the reference cell for each of the 74 columns.
2. Copied/dragged to next row.
3. Higlighted new row. Used find and replace all to change the reference sheet for the whole row - Repeated 25 times.