Greetings to all.
I have an Excel which matches Column cells' contents (from different sheets of an Excel) and track/display the values.
Formula I used (to compare 'ToHere' sheet with 'FromHere' sheet to get values):
Formula:
Please Login or Register to view this content.
:
:
so on until the End of the Row (Sheet name: 'ToHere')
Issue:
I get #N/A in between the shown values (seems issue with my written INDEX formula..??)
Can someone help me solve this issue, please.
Thanks
NA issue.png
Last edited by LesliePrabakar; 07-15-2016 at 04:57 AM. Reason: Attached Excel file
Welcome! An image is of little use. Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
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.
Thanks dear Ali for your quick response.
I tried uploading the Excel, but unable to upload from Chrome as well as Firefox browsers.
When I click on attachment icon, I get no uploading option.
Any help welcome.
Read my instructions again - do not try to use the paperclip icon (it doesn't work).
Took time to identify the option. Now done, Ali. Can somebody help now please.
Hi
I'm trying to solve
Formula:Please Login or Register to view this content.
but you can try VLOOKUP
Formula:Please Login or Register to view this content.
Try fixing your ranges:
=INDEX(FromHere!$B$2:$B$1356,MATCH(A2,FromHere!$A$2:$A$1356,0))
Dear José Augusto, I am awestruck with the formula shared.
Super cool, working fine.
The last row value is not retrieved rightly (displayed as #N/A), any clue please.
Please see the attachment for reference. Last value issue.png
This means there is no matching data.
you can type the formula like this as well "=INDEX(FromHere!B:B,MATCH(A2,FromHere!A:A,0))"
As you haven't lock the cell range from B2 to B1356 or A2 to A1356, when you click down the formula, excel will autumnally change the data range eg. B2:B1356 , then B3:B1357 and so on.....the array that index uses to find the value will be different (data base).
As there are two columns only, you can use vlookup instead, "=VLOOKUP(A:A,FromHere!A:B,2,FALSE)
It is not advisable to use full ranges such as A:A - it can slow down the processing, as the entire column has to be checked. It is much better to get the range correct and fix it, e.g. $A$1:$A$3000.
Originally Posted by LesliePrabakar
This problem with rang not with formula
Just try
=IFERROR(INDEX(FromHere!B:B,MATCH(A2,FromHere!A:A,0)),"") and drag down!!
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Thanks Ali for the formula and help.
Datum available for the last row. Can you check it and help please..
'Zylog' is the last available row from 'FromHere' sheet (reference value is 3.85).
ZYLOG 3.85
Leslie
Try to use
Formula:Please Login or Register to view this content.
and copy down
Note:Adjust your range for cover the table in FromHere sheet
See the file
Thanks José Augusto
Completely my query is solved.
Take good care all.
Thanks for the rep!
What if there are more columns in between 'Name' and 'Price' columns of 'FromHere' sheet.
Formula needs a small change (as few columns are added).
May I request to look up this attachment.
Just change the 2 in the formula to 4.
Wow Ali, again my query resolved. THANK YOU SO MUCH.
No problem. That number in a VLOOKUP query tells Excel which column in the defined array to look at to return the answer.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks