Find a number that matches the most significant digits and provide row / column result

[Bobbbo]

See attached Spreadsheet.

I have a spreadsheet that has a matrix of numbers in A2:D9. The number can be of lengths 3 to 6 digits with the possibility some cells are blank. I have a column of 6 digit codes found in A13:A16 that I need is a formula that will find the row that matches the first 3 or 4 or 5 or 6 digits then display the Value of that row across for the match in Column E2:E9 and display it in the "type" column B13:B16 and that row across for the match in Column F2:F9 and display it in the"Multiplier" column C13:C16 adjacent to the "Values to search" found in A13:A16.

The correct result are show in cells B13:C16 but they need to be replaced with a formula. The Values to search will always be 6 digits in length but the codes it is look to match can be of any length.

I hope that makes sense??

Thanks in advance for the help.

[tim201110]

Please Login or Register  to view this content.

[Bobbbo]

Please Login or Register  to view this content.

Is there a formula I can use instead of a macro?

[tim201110]

i wish i could find a solution with worksheet formulas
there is no macro in my solution, it is a user defined function

[kvsrinivasamurthy]

ARRAY formula in E13 , then dragged across upto F17

Please Login or Register  to view this content.

[FlameRetired]

Another way.

Please Login or Register  to view this content.

[Bobbbo]

I have to apologize to all of you. I have been on the road and have limited Internet access. I will be able to evaluated your responses next week.

[congnt92]

Or you can try

Please Login or Register  to view this content.

(Finish by pressing ENTER)

Please Login or Register  to view this content.

(Finish by pressing CTRL SHIFT ENTER)

Then copied down.

[shg]

If you make all the codes the same length, then detabulate and sort, a simple lookup will do:

	A	B	C	D	E	F	G	H	I	J
1	Codes	Codes	Codes	Codes	Type	Mult		Code	Type	Mult
2	123	23587	457802	789543	A	2.00		123000	A	2.00
3	456789	895			B	2.75		128000	C	3.50
4	35846	587	128	215478	C	3.50		130000	D	4.25
5	35848	589	130	215480	D	4.25		132000	E	5.00
6	358501	591	132	215482	E	5.00		134000	F	5.75
7	35852	134	215484		F	5.75		136000	G	6.50
8	35854	595	136	215486	G	6.50		138000	H	7.25
9	35856	597	138	215488	H	7.25		215478	C	3.50
10								215480	D	4.25
11								215482	E	5.00
12	Value	Code	Type	Mult				215484	F	5.75
13	591548	591000	E	5.00			B13: =LOOKUP(A13, $H$2:$H$30)	215486	G	6.50
14	358465	358460	C	3.50			C13: =LOOKUP($A13, $H$2:$H$30, I$2:I$30)	215488	H	7.25
15	123456	123000	A	2.00				235870	A	2.00
16	138564	138000	H	7.25				358460	C	3.50
17								358480	D	4.25
18								358501	E	5.00
19								358520	F	5.75
20								358540	G	6.50
21								358560	H	7.25
22								456789	B	2.75
23								457802	A	2.00
24								587000	C	3.50
25								589000	D	4.25
26								591000	E	5.00
27								595000	G	6.50
28								597000	H	7.25
29								789543	A	2.00
30								895000	B	2.75