Results 1 to 15 of 16

20080511, 00:21 #1
 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, francisHope 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

20080511, 00:41 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080511, 14:30 #3
 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 ctrlshiftenter):
=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 leftmost 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

20080512, 16:29 #4
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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>
HTHRegards,
Rory
Microsoft MVP  Excel

20080512, 17:40 #5
 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

20080512, 17:53 #6
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20080515, 19:39 #7
 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, francisHope 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

20080517, 18:56 #8
 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, francisHope 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

20080517, 20:40 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080915, 08:26 #10
 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,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

20080915, 09:02 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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 onetoone 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>

20080915, 14:47 #12
 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, francizHope 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

20080916, 00:39 #13
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20080916, 04:30 #14
 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, francisHope 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

20080924, 05:29 #15
 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, francisHope 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