Vlookup partial string match help or other formula help.

[breg523]

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)

ID	Desc	Fee	MISC 1	MISC 2	MISC 3	MISC 4	Rate		ID 1	CARD TYPE 1	DESC 1		Rate 1	PerItem
1L5	L5 US REG	0.72	0	2	550	0.72	0.05%		1L5	Visa	US REG	U.S. Regulated	0.05%	$ 0.22
1L5	L5 US REG	0.72	0	2	550	0.72	#N/A		220	MasterCard	INTL CORPORATE	2.00%	$0.00
220	INTL CORPORATE	0	0	0	0	0	#N/A
220	INTL CORPORATE	0	0	0	0	0	2.00%

<tbody>
</tbody><colgroup><col><col><col span="8"><col><col span="4"></colgroup>

 

[WarPigl3t]

	A	B	C	D
1	data	data	data	data
2	data	data	data	data

<tbody>
</tbody>

It's obvious by what you explained that this table you provided is not in column order so I'm going to have to ask you to repost your dataset and insert column letters and rows into the dataset before I can help you.

 

[collin]

For the formulas in green and black, your lookup range doesn't include the "ID 1" column. Change L2:N3 to J2:N3 and it should fix it