im trying to pull the rate from Rate 1 (column N) using two different formuls, one using the ID and one using the Desc, The values in columns J-N is much larger in volume but just pulled those two. Is there a way to use a combination of the two ID and Desc or perhaps use only a partial string to match or why the ID wont match because there are alphanumeric values(i think) in column ID1? The ID is unique to each Desc. by the way however some have alphanumeric characters i cant figure out which and how to adjust them. Thank you for all and any input/help! im using excel13 for windows 7
=VLOOKUP("*"&A2&"*",J2:N3,5,FALSE)
=VLOOKUP("*"&A3&"*",L2:N3,3,FALSE)
=VLOOKUP("*"&A5&"*",J2:N3,5,FALSE)
=VLOOKUP("*"&B6&"*",L2:N3,3,FALSE)
[TABLE="width: 1067"]
<tbody>[TR]
[TD="align: left"]ID[/TD]
[TD="align: left"]Desc[/TD]
[TD="align: left"]Fee [/TD]
[TD="align: left"]MISC 1[/TD]
[TD="align: left"]MISC 2[/TD]
[TD="align: left"]MISC 3[/TD]
[TD="align: left"]MISC 4[/TD]
[TD="align: left"]Rate[/TD]
[TD][/TD]
[TD="align: left"]ID 1[/TD]
[TD="align: left"]CARD TYPE 1[/TD]
[TD="align: left"]DESC 1[/TD]
[TD] [/TD]
[TD="align: left"]Rate 1[/TD]
[TD="align: left"]PerItem[/TD]
[/TR]
[TR]
[TD="align: left"]1L5[/TD]
[TD="align: left"] L5 US REG [/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0.05%[/TD]
[TD][/TD]
[TD]1L5[/TD]
[TD]Visa[/TD]
[TD]US REG[/TD]
[TD]U.S. Regulated[/TD]
[TD="align: right"]0.05%[/TD]
[TD] $ 0.22 [/TD]
[/TR]
[TR]
[TD="align: left"]1L5[/TD]
[TD="align: left"] L5 US REG [/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]220[/TD]
[TD="align: left"]MasterCard[/TD]
[TD="colspan: 2, align: left"]INTL CORPORATE[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]220[/TD]
[TD="align: left"]INTL CORPORATE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]220[/TD]
[TD="align: left"]INTL CORPORATE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="8"><col><col span="4"></colgroup>[/TABLE]
=VLOOKUP("*"&A2&"*",J2:N3,5,FALSE)
=VLOOKUP("*"&A3&"*",L2:N3,3,FALSE)
=VLOOKUP("*"&A5&"*",J2:N3,5,FALSE)
=VLOOKUP("*"&B6&"*",L2:N3,3,FALSE)
[TABLE="width: 1067"]
<tbody>[TR]
[TD="align: left"]ID[/TD]
[TD="align: left"]Desc[/TD]
[TD="align: left"]Fee [/TD]
[TD="align: left"]MISC 1[/TD]
[TD="align: left"]MISC 2[/TD]
[TD="align: left"]MISC 3[/TD]
[TD="align: left"]MISC 4[/TD]
[TD="align: left"]Rate[/TD]
[TD][/TD]
[TD="align: left"]ID 1[/TD]
[TD="align: left"]CARD TYPE 1[/TD]
[TD="align: left"]DESC 1[/TD]
[TD] [/TD]
[TD="align: left"]Rate 1[/TD]
[TD="align: left"]PerItem[/TD]
[/TR]
[TR]
[TD="align: left"]1L5[/TD]
[TD="align: left"] L5 US REG [/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0.05%[/TD]
[TD][/TD]
[TD]1L5[/TD]
[TD]Visa[/TD]
[TD]US REG[/TD]
[TD]U.S. Regulated[/TD]
[TD="align: right"]0.05%[/TD]
[TD] $ 0.22 [/TD]
[/TR]
[TR]
[TD="align: left"]1L5[/TD]
[TD="align: left"] L5 US REG [/TD]
[TD="align: right"]0.72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]0.72[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD]220[/TD]
[TD="align: left"]MasterCard[/TD]
[TD="colspan: 2, align: left"]INTL CORPORATE[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]$0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]220[/TD]
[TD="align: left"]INTL CORPORATE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]220[/TD]
[TD="align: left"]INTL CORPORATE[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.00%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="8"><col><col span="4"></colgroup>[/TABLE]