• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match formula error

aamirsq

aamirsq

Member
This formula suppose to return any value from C8:C567 based on matching value A2.


'INDEX(dps!$C$8:$C$567,MATCH(A2,$AHR$8:$BBS$567,0))


but match here return #NA. Why ?


btw column C has alphabets, #s & empty cells
 
Aamirsq


The #N/A error means there is no match in the Match() function


Are you sure there is a match ?


Sometimes values in either A2 or $AHR$8:$BBS$567 could have leading or trailing spaces which causes the values to not match

Can you check that?


If these don't help can you post a sample file?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
both $AHR$8:$BBS$567 & $C$8:$C$567 has empty cells.


I am pretty sure that A2 will match as if i just random search any cell using excel search it matches.
 
Hi Aamir,


Code: 
MATCH(A2,$AHR$8:$BBS$567,0)


You are searching A2 in AHR Column.. not in AHR to BBS Column.. Match only search in single Dimentional Array..


You need to go for some trick to do the same..


Match + Hlookup will work.. and many other way.. :)

Requesting to upload sample file.


Regards,

Deb
 
Dear Amir,

Debraj Roy has provided the correct solution. Try the formula

INDEX(dps!$C$8:$C$567,MATCH(A2,$AHR$8:$AHR$567,0))


Regards,


Muneer
 
i hope this will work ...


https://docs.google.com/file/d/0B5X-W23JaELNdm13RS1MU1FOcFE/edit?usp=sharing


btw all above your solutions results #N/A
 
In Sheet4! C2

The formula:

=INDEX(dps!$C$8:$C$567,MATCH(A2,dps!$AHR$8:$AHR$567,0))

works fine

Copy it down

Then goto C236


Most of the values aren't present in AHR8:567
 
'=IFERROR(INDEX(dps!$C$8:$C$567,MATCH(A2,dps!$AHR$8:$AHR$567,0)),"")


i thought it was working but it is only partially working, WHY ?
 
Did you notice that the Match range is on sheet dps!

Where your original post missed that important point
 
@aamirsq


Hi


Try This


=IFERROR(INDEX(dps!$C$8:$C$567,MATCH(A2,dps!$AHR$8:$AHR$567,0)),"")


its working


Thanks


SP
 
Hi Aamir,


In C2 Write Formula as:


Code: 
=INDEX(dps!$C$8:$C$567,MATCH(A2,INDEX(dps!$C$8:$BBS$567,,MATCH(B2,dps!$C$6:$CJX$6,0)),0))


Hope its working as per your requirement..

https://dl.dropbox.com/u/78831150/Excel/index-match-formula-error%28aamirsq%29.xlsx


Regards,

Deb


EDIT: CSE not required..
 
Thank you but i did not get this point


INDEX(dps!$C$8:$BBS$567,,MATCH(B2,dps!$C$6:$CJX$6,0) ??


btw i was i trying my luck with double index, above is new for me !!
 
Hi Aamir,


Is the provided solution working!!!


BTW.. Just yesterday OSCAR write a good post only for INDEX.. check the link.. you will never confused for any Index function.. :)


http://www.get-digital-help.com/2013/03/15/index-function-explained/


Regards,

Deb
 
You must log in or register to reply here.
Back
Top