#### cameronWFA

##### New Member
Hey Y'all,

Here is a simplified example for the spreadsheet I am trying to work with. I don't know how to post the sheet on the forum so it is just attached as an image.

Essentially, I want the cell that says "FORMULA CELL" to check the contents of the cell under the collumn "TOP DIVIDEND PAYING STOCK" (The cell under that collumn that is in its same row), and match it to the name that goes across row 3 (C3:G3). Then, after it has matched that, I want it to display the numerical value of that located collumn for the same row as "FORMULA CELL" and also the stock name it looked up under TOP DIVIDEND PAYING STOCK.

I hope that made sense, it is actually not all that complicated of a task I don't think. Here is the formula I am using now which I cant get to work correctly so that I can drag it up and it finds the value for each stock as it looks each one up:

Code:
``=INDEX(C4:G9, 9,MATCH(B9,\$C\$3:\$G\$3), 0)``

So basically, it is
INDEX(range, [row9], [match b9 to c3:g3])

And then I would ideally want it to output 32, the value in E9. I am hoping to get this thing working so I can drag the cell up and it would perform the same index/match function for row 8,7,6,5,etc... But currently I get the output "#VALUE"

Thank you all so much in advance for all your help. You guys rock!

Best,
Cam

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I don't follow ,ainly because I didn't see any image, but this will not work:
=INDEX(C4:G9, 9,MATCH(B9,\$C\$3:\$G\$3), 0)
because C4:G9 is 6 rows tall and you're requesting row 9 from those 6 rows. Not going to work.

INDEX(range, [row9], [match b9 to c3:g3])

The most important thing to understand is that
[row9] part is not a Row# persay.
It's an Index #.
The nth Row within the referenced range.

So you referenced range C4:G9
Trying to go to the 9th row.
But there are only 6 rows (4 5 6 7 8 & 9) in C4:G9
So it's a Ref! Error.

INDEX(A1:A10,9) = A9 (the 9th row in A1:A10)
INDEX(A3:A17,9) = A11 (the 9th row in A3:A17)

Hope that helps.

Here's the picture! Sorry I forgot to attatch it.

Ah, I see. However even when I change that to the correct "index row" in this case 6, I still get #VALUE as an error.

Code:
``=INDEX(C4:G9, 6,MATCH(B9,\$C\$3:\$G\$3), 0)``

OK: Here is an image with the code in the top, and the output I am getting. I am now getting a value (32), however this value is incorrect. With the code I am using in that cell, I am trying/it should display cell F4, with the value 34. And then ideally I want to be able to drag it down, and the next one below it would say 34, a readout of Verizon (E5).

Im pullin my hair out here!! ;]

Best,
Cam

You're trying too hard:
=INDEX(C4:G4,MATCH(B4,\$C\$3:\$G\$3,0))
and fill down.

You're trying too hard:
=INDEX(C4:G4,MATCH(B4,\$C\$3:\$G\$3,0))
and fill down.

That worked great, Bob. Thanks a lot for the help.

Best,
Cam

Replies
1
Views
203
Replies
8
Views
287
Replies
6
Views
592
Replies
3
Views
92
Replies
9
Views
765

1,203,742
Messages
6,057,112
Members
444,905
Latest member
Iamtryingman

### 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.

### Which adblocker are you using?

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

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