Index & Match issues

[hessyb2]

Hi,

i am trying to return values from a previous column by using match within index. it appears that i have done it corretly however rather then only returning the match value, it is returning all values from the selected column. my data is unsorted and i have made the match value text.

here is the formula that i have used, as i said i am not sure where i have gone wrong.

=INDEX('SPA Report'!A1:O500,(MATCH("BREACH",'SPA Report'!K$1:K500,0)),2)

thanks.

 

[Marcelo Branco]

Hi and welcome

I'm not understanding what you mean by
"..rather then only returning the match value, it is returning all values from the selected column."

Are you applying the same formula in multiple cells?

M.

 

[hessyb2]

thank you,

yes i have dragged it down over 500 rows.

it is just returning the value in the equivalent row of the tab that i am pulling the data from (SPA report) and not returning only "BREACH" values.

thanks

 

[Marcelo Branco]

thank you,

yes i have dragged it down over 500 rows.

it is just returning the value in the equivalent row of the tab that i am pulling the data from (SPA report) and not returning only "BREACH" values.

thanks

Use absolute references like (observe the $ symbols)

=INDEX('SPA Report'!$A$1:$O$500,(MATCH("BREACH",'SPA Report'!$K$1:$K$500,0)),2)

Then you can copy down and the formulas will refer to the same ranges, but you have to change "BREACH" to a cell reference to get the proper results

HTH

M.

 

[hessyb2]

i had already tried that.

when i absolute reference, the only value that is returned is that of the 1st row from the data source.

 

[Marcelo Branco]

i had already tried that.

when i absolute reference, the only value that is returned is that of the 1st row from the data source.

Yes.

Could you post a small sample of your data and expected results? So we can see what you need.

To post a sample of your data:
Select a relevant part of your data, say, 10/15 rows including headers
Put borders
Copy (ctrl+C) and paste (ctrl+V) in the forum reply page

M.