SOLVED

Index/Match Error

Copper Contributor

I'm using the Index/Match function to retried a value from a corresponding cell along with a IFERROR statement for "-" if no match - but not getting the desired results.

 

=IFERROR(INDEX('Daily SSA Phones'!$I:$I,MATCH($C$41,'Daily SSA Phones'!$R:$R,0)),"-")

 

Formula is in cell D41 and I'm trying to match two names, one in the cell right next to it (C41) with another from another sheet "Daily SSA Phones" somewhere in Column R and then return the value that corresponds with the name on the same "Daily SSA Phones" sheet Column I. In other words, I want to find the name associated with the number of calls that person made from the "Daily SSA Phones" sheet. If there is no match, it should return a value of "-".  Looking for feedback and/or suggestions on what I could be doing wrong since I'm not getting the correct value when I spot-check for accuracy.

9 Replies

Formula looks correct, perhaps an issue is in your data - texts could include spaces or other invisible characters, thus no match. Try to check manually will formula like this returns TRUE or not

=C41='Daily SSA Phones'!R8

if in R8 it shall be the same value as in C41

 

Thank you - each test has returned "FALSE" - is there a way that I should format the data?

It depends on which extra characters do you have. If you copy/paste your  Daily SSA Phones from Web that could be nonbreaking spaces (unicode 160) or like. That could be spaces. First is to define which invisible characters do you have, after that use Ctrl+H to replace them on nothing. Perhaps formula also could be modified, but again, it depends on which symbols and where they are.

 

 

 

I'm taking this data and copy/paste values into my master spreadsheet.  

This is the master file.  As you can see, the data is correct in some places (Internal Sales tab corresponding with the Daily SSA Phones) but isn't correct in others.

best response confirmed by jrusten (Copper Contributor)
Solution

Hi,

 

You current formula is

=IFERROR(INDEX('Daily SSA Phones'!$I:$I,  MATCH(C13,'Daily SSA Phones'!$R:$R)  ,0),"-")

where the MATCH is with missed third parameter. If it is missed the default value is 1. That means if the name is not found MATCH returns next closest in alphabetical order name in the list.

 

I guess you'd like to have exact match  (otherwise why IFERROR) and the formula shall be

=IFERROR(INDEX('Daily SSA Phones'!$I:$I,  MATCH(C13,'Daily SSA Phones'!$R:$R,0)  ),"-")

 

Thank you for all your help!  That worked perfectly!

@Sergei Baklan @jrusten 

 

Hello guys!,

I have a very similar issue with INDEX/MATCH, the only difference is that I'm using the function ROWS, on the "look_Value" of the MATCH formula. I already did a lot of trouble shooting, I saw videos and read another blog and this answer before is the nearest option that I found, bit I still have the same issue #Value!, is there anyway that you can help me please? This is the formula I'm using:

 

=IFERROR(INDEX(Checks!C:C,MATCH(ROWS(F$4:F13),Checks!A:A,0)),"")

 

 

@MedRey32 

I can't reproduce error with your formula (attached), perhaps you may share small sample file?

1 best response

Accepted Solutions
best response confirmed by jrusten (Copper Contributor)
Solution

Hi,

 

You current formula is

=IFERROR(INDEX('Daily SSA Phones'!$I:$I,  MATCH(C13,'Daily SSA Phones'!$R:$R)  ,0),"-")

where the MATCH is with missed third parameter. If it is missed the default value is 1. That means if the name is not found MATCH returns next closest in alphabetical order name in the list.

 

I guess you'd like to have exact match  (otherwise why IFERROR) and the formula shall be

=IFERROR(INDEX('Daily SSA Phones'!$I:$I,  MATCH(C13,'Daily SSA Phones'!$R:$R,0)  ),"-")

 

View solution in original post