LinkBack URL
About LinkBacks
I have used INDEX and MATCH for doing lookup with 2 parameters. It seems it is working fine in Column (R) of first sheet but some items like R26 returns #N/A and I can not find the reason; Could you please guide me?
I have used INDEX and MATCH for doing lookup with 2 parameters. It seems it is working fine in Column (R) of first sheet but some items like R26 returns #N/A and I can not find the reason; Could you please guide me?
Last edited by omid020; 04-05-2017 at 07:24 AM.
1st, try this regular formula instead of that array formula...
=INDEX(ERP!$C$4:$C$2000,MATCH(A4&" "&F4,INDEX(ERP!$A$4:$A$2000&" "&ERP!$B$4:$B$2000,0),0))
2nd you are getting errors because there is no match
BRLC
ERP
A1-INTERIOR-082
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
The only thing I can say is that in row 26
(ERP!A26:A1708='BRLC-TRIM-H320 +V0-951118'!A26)*(ERP!B26:B1708='BRLC-TRIM-H320 +V0-951118'!F26)
returns a large array of all zeros. There is nothing there that meets the criteria. That is why MATCH is returning #N/A.
Do you know what it is you expect it to return? Have you checked for leading/trailing spaces in your data source? Have you tried the 'Find' / 'Find all' feature? 1700 rows is a lot to search manually especially when we don't know what we are looking for.
Dave
There are discrepancies. In the case of R26, the part name on your first sheet is:
A1-8075-TOUCHUP-BLACKOUT-3
but on the ERP sheet it is:
A-8075-TOUCHUP-BLACKOUT-3
Your matches need to be exact for this to work.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
In sheet "BRLC-TRIM-H320 +V0-951118" in cell "F26' "A1-INTERIOR-082 " after 082 two blank space. If you remove blank space "R26" showing "301"
And in "EFP" sheet cell "B577" data is : "A1-INTERIOR-082" after 082 no space.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
I am having the same problem, I've checked and the names are good.
On 'Reports!' Column L, the rows 10:15 are giving me #N/A!
I have checked and the references are good.
Even more, if I move the referenced row on 'Fulcrum Export first page!' (from row 3 to 4 for example) it works!
I don't know what to try anymore, I am lost!
Thank you very much for having a look
Best regards
Eva
Last edited by EvaJ; 04-05-2017 at 05:35 AM.
Dear EvaJ, This is separat issue kindly post new thread.
Thank you all! Yes it seems somewhere spaces and somewhere addition or shortage of one character causing that issue.
Sorry, I will post it apart.
Thank you
You're welcome!Originally Posted by omid020
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks