Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup to include wildcard* (Excel 2003)

    Hi

    I have a table in sheet3 of a workbook named "Process.xls"contain customers' codes in ColumnA with at least 4 Characters, eg 3LEY.
    I have an application which produce for the same customers list but with customers codes of 6 - 10 characters in Column B
    on sheet1 of workbook "FXAppl.xls". However, the first 4 characters is always the same.

    Is is possible to use a VLookup or Index / Match function in column N on sheet1 of the
    "Process.xls" to lookup the exchange rate value in column C on the sheet1 of the "FXAppl.xls"
    by using the codes mentioned above given that the differences in codes on both sheets of the respective workbooks

    TIA

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    I'd create auxiliary columns in both worksheets that extract the first 4 characters, using formulas similar to

    =LEFT(A2, 4)

    You can then create the usual INDEX/MATCH formulas using these auxiliary columns.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    A way without the intermediate columns is to use an ARRAY function. If I understand your setup in N1 you can use (confirm with ctrl-shift-enter):

    =INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MIN(IF(LEFT(Sheet3!A1,4)=LEFT([FXAppl.xls]Sheet1!$B$1:$B$100,4),ROW([FXAppl.xls]Sheet1!$B$1:$B$100))))

    It compares the left-most characters of A1 in sheet3 (of Process.xls) to the left most chars in column B1:B100 of Sheet1 of FXAppl.xls. It gets to first row (min(row) so it gets the first match. It uses that value with the index function on C1:C100 of Sheet1 of FXAppl.xls.

    Steve

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    You can use wildcards with MATCH, so this should work:
    <code>=INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A1,4)&"*",[FXAppl.xls]Sheet1!$B$1:$B$100,0))</code>
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    I never realized that MATCH could take wildcards!

    Thanks for the information. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Steve

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Yep - you have to use 0 as the final argument for it to work.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi Rory and Steve

    My apology for the late reply. Thanks.I will test the formula and let you know the result.Something new that I learn when I come here.

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi Rory,

    Thanks, its work great. How do I hide #NA?
    Any possiblity to insert the formula into the column via macro till the last non empty rows or write this in macro?

    Thank you in advance.

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    To suppress #N/A you can use a formula like this:

    =IF(ISNA(MATCH(LEFT(Sheet3!A1,4)&"*",[FXAppl.xls]Sheet1!$B$1:$B$100,0)),"",INDEX([FXAppl.xls]Sheet1!$C$1:$C$100,MATCH(LEFT(Sheet3!A1,4)&"*",[FXAppl.xls]Sheet1!$B$1:$B$100,0)))

    You can double click the fill handle (the black square in the lower right corner of the cell) to fill down the formula.

  10. #10
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi

    I have change the FXAppl to Sheet5 of the "Process.xls" and the data in the Sheet3 of "Process.xls" renamed to OLTCode and
    rearranged the Customers' codes, 4 characters, to Column B.
    In Sheet5, the rates that I am looking up are in column I and the Customers codes that are from 6 - 10 characters are in Column C


    =IF(ISNA(MATCH(LEFT(Code!$B$2:$B$45,4)&"*",Sheet3! $C$1:$C$100,0)),"",INDEX(Sheet3!$I$1:$I$100,MATCH( LEFT(Code!$B$2:$B$45,4)&"*",Sheet3!$C$1:$C$100,0)) )
    The formula doesn't works as expected, some cells does produce the rates and some don't produce even the Customers codes are
    show. Not sure what is wrong with my formula.

    Thanks

    regards,
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Your workbook doesn't make sense to me. You're using formulas in Sheet1 to match values in Code to a range in Sheet3, but the values in Sheet1 don't have a one-to-one relationship with those in Code. Moreover, there is no Sheet5 in the workbook you posted. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  12. #12
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi Hans

    Sorry for the confusion as I posted this in the late hours ( or early hours ).
    The col B of "Code" sheet lists the customers' codes but most are in 4 characters,
    In Sheet3 lists the information of customers deals, you will notice that in col C are the client id which are from 6 to 10 characters,
    and that will have the same first 4 characters with the first 4 characters in the col B of the "Code" sheet.
    In col I of sheet3 are the rates that I am looking for that I want to fill these rates in col N of the sheet1.

    Hope I have made it clearer now.

    Thanks

    regards, franciz
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    As far as I can tell, the sheet Code has nothing to do with this.

    Try this formula in N2 on Sheet1:

    =IF(ISNA(MATCH(LEFT($M2,4)&"*",Sheet3!$C$1:$C$100, 0)),"",INDEX(Sheet3!$I$1:$I$100,MATCH(LEFT($M2,4)& "*",Sheet3!$C$1:$C$100,0)))

    and fill down to N24.

    Note: calculation has been set to Manual in your sample workbook, so the formulas will not update automatically.

  14. #14
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi Hans

    I will test it tomorrow with real data at work. I am using the Code sheet for the vlookup to get the value into col M.

    As always, I appreciate your help.

    Thanks

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  15. #15
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup to include wildcard* (Excel 2003)

    Hi Hans

    The formula is not working properly as it show the header under Sheet3 in cells where there is no rate (Dealt date), it should show a blank instead. Further, what if I do not have a helper column in col M meaning that insteadd of using LEFT($M2,4)&"*",
    there is another sheet, should I be replace as LEFT(SheetName!A2: B1000,4)&"*",

    TIA

    regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •