Lookup with a fill down based on another field - how the heck do I explain this :)

[MattDanFrank2]

Hi there,

As always, thanks in advance for any help. Thank you. This one is bending my little brain.

I have two tables with serial numbers.

The first table has (let's call it the master table) has serial numbers and the related account numbers.

The second table (let's call is the slave table) has serial numbers and related account names (but no account numbers).

The objective is to lookup the account number from the first table and place it on the second table.

But, and here's the twist, the second table has many serial numbers that are not in the first table, but there will always be at least one that matches.

Take the first example. In table 2 there is serial number 1 related to Jim Ltd, which relates to account number 1001 in table 1. What I need the formula to do is populate any other instances of Jim Ltd with account number 1001.

In the second example, in table 2 there is serial number 6 related to Frank Corp, which relates to account number 1002 in table 1. What I need the formula to do is populate any other instances of Frank Corp with account number 1002.

I hope that make sense.

Matt

p.s. I have tried to install XL2BB but my work laptop won't let it work.

 

[shinigamilight]

Try this

=VLOOKUP(MIN(IF([Company Name]=[@[Company Name]],[Serial Number])),Table1,2,0)

 

[MattDanFrank2]

Hi shinigamilight,

You've solved it!!! Thank you!!!

There's a wrinkle I forgot to mention though...I'm my example, I used serial numbers that were numbers only, in my real life example, however, they are alphanumeric, and I'm running into #N/A as a result. I think I've also got the issue of numbers stored as text. Urrghhh. Is there another function you can use that can brute-force around this? Any help or further advice would be greatly appreciated.

I really appreciate your help.

 

[shinigamilight]

You will have to post more sample data for that

 

[MattDanFrank2]

Thanks for your patience. Here is a sample:

7581200005
8022579763
18350412029
19350322030
19350322031
19350322032
9.45E+11
4.42609E+12
7.52714E+12
7.52991E+12
(654e)
1095KSPOR
201911S002A030
22D21416
22E04260
22E04443
22E06452
22E23905
22E24469
22E26213
22E26313
22E30548
22F04323
22F18918
2318W520146
235-121-4809
25Y03012
27Q01105
2CG02653
2ER02452
2ER02759
2ER02967
2ER03056
CNCJ369251
CNCJ36930
CNCJ369301
CNCJ36939
CNCJ369391
CNCJ36941
CNCJ369411
CNCJ36988

 

[MattDanFrank2]

Hi shinigamilight,

I hope you're well - thank you again for all your help so far. I'm hoping you are able to help with this alphanumeric lookup challenge, too, please. If not, any guidance or direction is appreciated.

Best wishes and thanks, Matt

 

[shinigamilight]

DO you have xl2bb? Upload some data properly highlighting the lookup table and the table in which you need the output.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[MattDanFrank2]

Hi shinigamilight,

After manually installing XL2BB, I got it working successfully. OK, here are two versions, one with the simple Serial Numbers and your original formula. And second with the alphanumeric Serial Numbers and your original formula. Maximum thank you.

Serial Number	Account Number		Serial Number	Company Name	Looked Up Account Number
1	1001		1	Jim Ltd	1001
2	1001		99	Jim Ltd	1001
3	1001		129	Jim Ltd	1001
4	1001		324	Jim Ltd	1001
5	1001		532	Frank Corp	1002
6	1002		6	Frank Corp	1002
7	1002		432	Frank Corp	1002
8	1003		43	Jill Biz	1003
9	1003		9	Jill Biz	1003
10	1004		8	Jill Biz	1003

Serial Number	Account Number		Serial Number	Company Name	Looked Up Account Number
CNCJ369251	1001		CNCJ369251	Jim Ltd	#N/A
CNCJ36988	1001		QW12133	Jim Ltd	#N/A
CNCJ369411	1001		WQ32132132	Jim Ltd	#N/A
2CG02653	1001		WQ2323323255	Jim Ltd	#N/A
27Q01105	1001		WQ6546546545	Frank Corp	#N/A
201911S002A030	1002		201911S002A030	Frank Corp	#N/A
22E24469	1002		QW7657657657665	Frank Corp	#N/A
235-121-4809	1003		WQ87876876876	Jill Biz	#N/A
1095KSPOR	1003		1095KSPOR	Jill Biz	#N/A
(654e)	1004		235-121-4809	Jill Biz	#N/A

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F
1	Serial Number	Account Number		Serial Number	Company Name	Looked Up Account Number
2	CNCJ369251	1001		CNCJ369251	Jim Ltd	1001
3	CNCJ36988	1001		QW12133	Jim Ltd	1001
4	CNCJ369411	1001		WQ32132132	Jim Ltd	1001
5	2CG02653	1001		WQ2323323255	Jim Ltd	1001
6	27Q01105	1001		WQ6546546545	Frank Corp	1002
7	201911S002A030	1002		201911S002A030	Frank Corp	1002
8	22E24469	1002		QW7657657657665	Frank Corp	1002
9	235-121-4809	1003		WQ87876876876	Jill Biz	1003
10	1095KSPOR	1003		1095KSPOR	Jill Biz	1003
11	(654e)	1004		235-121-4809	Jill Biz	1003
Lists

Cell Formulas
Range	Formula
F2:F11	F2	=TAKE(SORT(XLOOKUP(FILTER($D$2:$D$11,$E$2:$E$11=E2),$A$2:$A$11,$B$2:$B$11,"")),1)

 

[shinigamilight]

You can try this.

=LET(a,XLOOKUP(FILTER($D$2:$D$11,$E$2:$E$11=E2),$A$2:$A$11,$B$2:$B$11,"",0),DROP(FILTER(a,a<>""),COUNTA(FILTER(a,a<>""))-1))