Hi,
Can Index Match be used for different data length and across multiple workbooks?
e.g.
Workbooks A , data Range("G13:G50")
Workbooks B , data Range("G13:I600")
the lookup value is G13 to G50 in Workbooks A, while the returned value is from I13 to I600 in Workbooks B
can anyone help me?
Hi Faridwahidi ,
I think it is not possible for various ranges .
Punnam
Hi Faridwahidi,
Can you upload a sample workbook.
If FUNCTION can't do, maybe VBA can do, but have to look at your sample file 1st.
-If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
-Always upload a workbook before start your question
To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.
+++ If my answer(s) helped you, please add me reputation by click on * +++
Hi wenqq3,
I dont want to use VBA and I think it is not required because it can easily be solved using VLOOKUP.
But when I read statement that INDEX(MATCH) is more accurate with the large data scale compared to VLOOKUP. I just want to try apply it.
Is INDEX(MATCH) not applicable with the differences data length?
INDEX(MATCH) to be placed in column J, starting with J13 in workbooks A and the lookup array in workbook B column G13:I632
The lookup value is G13:G83 and I want to return value in column I in workbooks B
Last edited by Faridwahidi; 09-16-2014 at 06:07 AM.
Please Login or Register to view this content.
Type this code in WorkBook A: J13, and CTRL + SHIFT + ENTER
hi wenqq3,
Its returned #N/A,I already use CTRL+SHIFT+ENTER (Curly Braces)
Oh yeah, forget tellyou that both your "PF No" is not match.
A is "000" xxxxx, B is xxxxx. Please made them equivalent
Hi wenqq3,
Can we exclude "PF No" column as it is not the return value that I looking for? I only want the return value at column "I".
using VLOOKUP just simple like this
I am a bit confused with the INDEX(MATCH) syntax.Please Login or Register to view this content.
Is the INDEX(MATCH) syntax across multiple workbooks/worksheets different compared to syntax in the same worksheets?
Last edited by Faridwahidi; 09-17-2014 at 10:34 AM.
function vlookup only single criteria, if you want lookup for multi criteria, Index+Match is better function.
So right now your question is:
Looking for "NAME" is sheet<A>, that match is sheet<B> then return "CERTIFICATION STATUS"?
If yes, vlookup can solve your problem.
Should be across 1 workbook/worksheet.Is the INDEX(MATCH) syntax across multiple workbooks/worksheets
Re: Index Match with different data length and across multiple workbooks
Hi
Try this..
Formula:
Please Login or Register to view this content.
Check the attached files
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
hi wenqq3,
Yes, I know VLOOKUP can solve the problem but I just want to know the alternative method. So, does it means that INDEX(MATCH) can't solve the problem?
In the meantime, I noticed that VLOOKUP can only working from left to right? If I have the data from right to look for left data , How to solve it?
1. INDEX(MACTH) definitely can solve your problem.
2. VLOOKUP can only working from left to right?Correct.
3. If I have the data from right to look for left data , How to solve it?
Either you shift your lookup data to left, or use another suitable function other than vlookup. (Maybe sumproduct/index(match), depend your question)
So right my post#5 is not solve your problem?
As I mentioned in post#6, it return #N/A. You may download both files (A & B) and try at your end.
WorkBook A: J13, and CTRL + SHIFT + ENTER,Please Login or Register to view this content.
if you wish return other value, please change $J$13:$J$632 to another range.
hi wenqq3,
Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks