• 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.

Lookup specific strings in tables containing wildcards

K

KJT

New Member
Hi all,

Looking for some help with the following, please:


I have a set of accounts in Excel where certain account codes are grouped (using SUMIFS) in various departments, and I need to be able to check whether all account codes in a Trial Balance have been mapped to the appropriate groupings in the management accounts.

Account codes are all in the format "nnn nnnnn" where n is a 0-9 digit.

The groupings contain wildcards ("?" characters), so for example a grouping may contain the code "123 5??00" (to sum the values for all codes that correspond to that code format). Any combination of wildcard characters can exist in any of the grouping codes.


I need to construct a formula to check, for example, that code "123 51100" is mapped to a grouping. Effectively I want to perform a VLOOKUP of "123 51100" into a range that contains "123 5??00" and return a result of "123 5??00", but obviously this does not work because of the wildcard characters.


Is there any way round this, without having to manually insert all the permutations of the codes containing wildcards into the lookup range?


Any suggestions much appreciated.


Thanks,

KJT
 
Hi ,


Can you explain in a little more detail ?


The value you wish to look up is "123 51100" ; let us assume the range you wish to lookup this value in , is A4:A400 ; within the range A4:A400 will the value "123 51100" occur ?


Within the range A4:A400 is there a value such as "123 52300" ? Or will there be only the value "123 5??00" ? Or will both be present ?


What should the lookup formula return if it finds a match ? what should the formula return if there is no match ?


Narayan
 
Hi,


Yes, using your example, I want to be able to look up a number of values including the value "123 51100" in the range A4:A400, but the specific value "123 51100" will not be in that range. Instead, the value "123 5??00" will be in that range.


Ideally therefore I want the lookup formula to return "123 5??00" as a match for the value "123 51100" being looked up.


If the next value in the list to be looked up was, for example, "123 52300", the lookup into A4:A400 should also return the value "123 5??00" as a match.


Does that help?


KJT
 
Hi ,


VLOOKUP can be used as follows :


=IFERROR(VLOOKUP(lookup_value,List,1,0),VLOOKUP(LEFT(lookup_value,5)&"??"&RIGHT(lookup_value,2),List,1,0))


Narayan
 
Hi,


Thanks for that, Narayan. However, that will only work where the wildcard characters occupy the 6th and 7th positions in the account code strings in the List.


What I was hoping to achieve was a way of recognising a match, if applicable, wherever the wildcards may be (they are different in each grouping), and for however many wildcards there are. So, for example, a lookup of the value "123 45678" into the range that contains "123 45???" would also return a match.


KJT
 
Hi ,


If I understand you correctly , what you are doing is matching the list with the input value , and not the other way around.


In such a case , the first match should be returned ; so if your lookup value is "123 45678" , and your range of groupings happens to have the following :


123 45???


as well as


1?? 45678


both will match ; which one is returned will depend on which one occurs first.


Can you confirm / clarify ?


Narayan
 
Hi,


Yes that's true. However, the range of groupings should be such that, taking wildcards into account, a value should not occur twice. In your example, 1?? 45678 and 123 45??? would (should) not both exist in the range, as they would both hold true for the value 123 45678.

Put another way, all the possible values (resulting from the wildcard permutations) in the range of groupings should only exist once in the range.


KJT
 
Hi KJT,

Give a try with the below UDF. Copy it to the workbook module and use the formula on the worksheet. Its not a refined version, but can make it better if this works for you.

[pre]
Code: 
Public Function MYLookup(lookup_value As String, table_array As Range) As String
Dim c As Range
Dim x As Integer
For Each c In table_array
For x = 1 To Len(lookup_value)
If Not (Mid(c.Text, x, 1) = "?") Then
If Not (Mid(lookup_value, x, 1) = Mid(c.Text, x, 1)) Then
Exit For
End If
End If
MYLookup = c.Text
Next x
If x = 10 Then Exit Function
Next
End Function
[/pre]
 
Hi Lohith,


You've lost me a bit on this. I've pasted the above into a module in the workbook, but can you confirm the formula to use in the worksheet, please?


Thanks,

KJT
 
Hi KJT,


Its the same name as the function. If you have this function in workbook module, then on any cell of your workbook enter

=mylookup(value to check, range to check)


eg.. =mylookup("1254367890", "a1:a400")
 
You must log in or register to reply here.
Back
Top