Index and Match formula not functioning

[bill351]

Hey Guys and Girls

I am puzzled, I have a spreadsheet with formulas for different section on the sheet 2 work perfectly the 3rd not so good all formulas are similar its just the Index data and match data accessing different columns.

My problem one is =INDEX(Data!F2:F13,MATCH(F3,Data!G2:G13,0),1) where the formula is in E3
Data!F2:F13 is the names
F3 is a drop down list with the names
Data!G2:G13 are the cost centers
and the result is in E3 #N/A ?? I have checked the names are the same the cell formatting is Ok, I have swapped columns around but nothing. I know its something simple Because the other 2 formulas sitting in columns A and C work just fine so I am hoping you can point out my error

Cheers from Oz

 

[Aladin Akyurek]

1. Just enter:

=INDEX(Data!$F$2:$F$13,MATCH(TRIM(F3),Data!$G$2:$G$13,0))

2. Control+shift+enter, not just enter:

=INDEX(Data!$F$2:$F$13,MATCH(TRIM(F3),TRIM(Data!$G$2:$G$13),0))

Does #2 succeed?

 

[bill351]

Sorry Aladin Akyurek the straight formula and the array formula didn't work still have the #N/A

 

[Aladin Akyurek]

Sorry Aladin Akyurek the straight formula and the array formula didn't work still have the #N/A

We are trying to diagnose...

What do you get when we run the following?

=COUNTIFS(Data!$G$2:$G$13,F3)

 

[bill351]

That come back as zero?? which is Odd as I am looking at the unit name

 

[bill351]

Sorry Aladin Didn't think Data!G2:G13 is the cost centers I put in countif for Data!F2:F13 which is the name in F3
Data!F2:F13 has come back as a count of 1

 

[Aladin Akyurek]

Sorry Aladin Didn't think Data!G2:G13 is the cost centers I put in countif for Data!F2:F13 which is the name in F3
Data!F2:F13 has come back as a count of 1

It seems you switched the ranges...

=INDEX(Data!$G$2:$G$13,MATCH(F3,Data!$F$2:$F$13,0))

 

[bill351]

**** it I cant believe I was that stupid to swap those 2 around GRRRRR swapped them and it all works just as it should

Really Sorry Aladin for wasting your time Thank you for all your help

 

[Aladin Akyurek]

**** it I cant believe I was that stupid to swap those 2 around GRRRRR swapped them and it all works just as it should

Really Sorry Aladin for wasting your time Thank you for all your help

No problem. You are welcome.