Beeyen
asked on
Vlookup and NA problem
Good Day Experts,
I have a wookbook (attached) with two speadsheet (Groupdates andGroupMemberCount) in which I wanted to match the totals from GroupMemberCount to Groupdates. I am using the simple vlookup function, using the group as the common field and the results no matter what I try return #N/A. I searched the two spreadsheet and the group do show in both spreadsheets but again the results for the total is #N/A. I am thinking it may have something to do with the way the spreadsheets are formatted. Would you please take a look and see if you can figure out what I can do to return the correct results. Thanks
I have a wookbook (attached) with two speadsheet (Groupdates andGroupMemberCount) in which I wanted to match the totals from GroupMemberCount to Groupdates. I am using the simple vlookup function, using the group as the common field and the results no matter what I try return #N/A. I searched the two spreadsheet and the group do show in both spreadsheets but again the results for the total is #N/A. I am thinking it may have something to do with the way the spreadsheets are formatted. Would you please take a look and see if you can figure out what I can do to return the correct results. Thanks
CompProbSolv
It does not appear that there is a file attached.
ASKER
ASKER
The file is now attached. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good Day Norie, That worked but can you explain the formula? Thanks
This part of the formula deals with the numeric/alphanumeric problem.
IFERROR(VALUE(C2),C2)
If the value in C2 is actually numeric but is stored as text then VALUE will convert it to numeric, if it's alpanumeric it's not converted.
This returns the third column in list1.
INDEX(list1,,3)
This finds the row in the third column of list1 where the value in C2 is found.
MATCH(IFERROR(VALUE(C2),C2
We use the value returned from this with a further INDEX function to return the value from the corresponding row in the 4th column of list1.
=INDEX(list1,rowfrommatch,4)
Hope that helps.
PS You could actually use VLOOKUP but you would need to convert the data in column C on GROUPDates so numbers stored as text are converted to actual numbers.
IFERROR(VALUE(C2),C2)
If the value in C2 is actually numeric but is stored as text then VALUE will convert it to numeric, if it's alpanumeric it's not converted.
This returns the third column in list1.
INDEX(list1,,3)
This finds the row in the third column of list1 where the value in C2 is found.
MATCH(IFERROR(VALUE(C2),C2
We use the value returned from this with a further INDEX function to return the value from the corresponding row in the 4th column of list1.
=INDEX(list1,rowfrommatch,4)
Hope that helps.
PS You could actually use VLOOKUP but you would need to convert the data in column C on GROUPDates so numbers stored as text are converted to actual numbers.
ASKER
Is this VB?
ASKER
And Thank you for you assistance
ASKER
Excellent! Thanks
It's not VBA (or VB).
I've only used standard Excel worksheet functions.:)
I've only used standard Excel worksheet functions.:)
ASKER
Thanks. It works nicely!