Tricky Index/Match Function. Please Help

[cameronWFA]

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:

=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

 

[BobUmlas]

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.

 

[Jonmo1]

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.

 

[cameronWFA]

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

 

[cameronWFA]

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.

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

 

[cameronWFA]

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

 

[BobUmlas]

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

 

[cameronWFA]

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