Using Index and Match Functions

I am trying to us the index and match functions to pull a value from a column when I specify the row the value is to be pulled from.

Capture.PNG

I want the value in column "C" to be displayed in "G2" when an "x" is in column "G". The "x" will specify the row in column "C" that the value is to be pulled from.

The formula I have in "G2" is as follows:
=IFERROR(INDEX(C9:C93,MATCH("x",G9:G93)),0)

The problem is the value isn't put in "G2" every time an "x" is in column "G" as seen in the picture. In the picture I want the value in "G2" to be 21.1 not 8.

Is this the route that I should be going to do what I am trying to do? or is there another way I could do this?

Originally Posted by etaf

how will excel know which X in the column to use ?

The higher row number will be how excel knows which "x" to use. The formula that I currently have works half the time. If I place an "x" right below the first "x" the value will change. But if I place an "x" 3 or 4 rows down the value will not change.

Originally Posted by FDibbins

Hi, welcome to the forum

Im a bit confused here

So where is the X? If that is in G2, then there cannot also be a formula there as well, a cell can contain only 1 thing = entry or formula

I am not trying to put an "x" and a formula in the same cell. I would like to be able to put an "x" between "G9" and "G15". When an "x" is in one of those cells the value in the same row and column "C" will be placed in "G2". When I add another "x" in a row further down column "G" the value will change to the corresponding value in column "C".

Does this make sense to try and do?

Originally Posted by martindwilson

=LOOKUP(2,1/(G9:G15="x"),C9:C15) will give the value in c for last x in g

This works great!! Thank you for your help!!