required lookup with different lengths

[ravi2628]

Hi everyone,

good morning/afternoon/evening,

I was looking for a formula where I can get an exact match of the lookup with certain criteria


Sheet 1:

Propofol Injection 200mg

Enoxaparin Sodium 60mg

Heparin Sodium 25000IU

Enoxaparin Sodium 40mg

Polymyxin B Sulphate 500000 IU

Sterile Noradrenaline Conc Inj 20mg

Vancomycin Hcl 500mg

Atracurium Besylate 50mg

Omeprazole Injection 40mg

Sterile Noradrenaline Conc Inj 8mg

L-Asparaginase 10000IU

Oxaliplatin 100mg

Doxorubicin Hcl 50mg

Cytarabine 500mg

Mapping Sheet:

Propofol

Enoxaparin Sodium

Heparin

Enoxaparin Sodium

Polymyxin B Sulphate

Sterile Noradrenaline Conc inj

Vancomycin Hcl

Atracurium Besylate

Omeprazole Injection

Sterile Noradrenaline Conc

Doxorubicin Hcl

Output:

Propofol Injection 200mg

Enoxaparin Sodium 60mg

Heparin Sodium 25000IU

Enoxaparin Sodium 40mg

Polymyxin B Sulphate 500000 IU

Sterile Noradrenaline Conc Inj 20mg

Vancomycin Hcl 500mg

Atracurium Besylate 50mg

Omeprazole Injection 40mg

Sterile Noradrenaline Conc Inj 8mg

L-Asparaginase 10000IU

Oxaliplatin 100mg

Doxorubicin Hcl 50mg

I have tried using min length with vlookup but it is not working correctly


Currently using formula is vlookup(concatenate(left(Cell,13),"*"),array,1,0)

but not able to get the exact output at some point.

please help me out with the formula.

Regards,
Ravi

 

[Fluff]

Is this what you want?

Fluff.xlsm
	A	B	C	D
1
2	Propofol Injection 200mg		Propofol	Propofol Injection 200mg
3	Enoxaparin Sodium 60mg		Enoxaparin Sodium	Enoxaparin Sodium 60mg
4	Heparin Sodium 25000IU		Heparin	Heparin Sodium 25000IU
5	Enoxaparin Sodium 40mg		Enoxaparin Sodium	Enoxaparin Sodium 60mg
6	Polymyxin B Sulphate 500000 IU		Polymyxin B Sulphate	Polymyxin B Sulphate 500000 IU
7	Sterile Noradrenaline Conc Inj 20mg		Sterile Noradrenaline Conc inj	Sterile Noradrenaline Conc Inj 20mg
8	Vancomycin Hcl 500mg		Vancomycin Hcl	Vancomycin Hcl 500mg
9	Atracurium Besylate 50mg		Atracurium Besylate	Atracurium Besylate 50mg
10	Omeprazole Injection 40mg		Omeprazole Injection	Omeprazole Injection 40mg
11	Sterile Noradrenaline Conc Inj 8mg		Sterile Noradrenaline Conc	Sterile Noradrenaline Conc Inj 20mg
12	L-Asparaginase 10000IU		Doxorubicin Hcl	Doxorubicin Hcl 50mg
13	Oxaliplatin 100mg
14	Doxorubicin Hcl 50mg
15	Cytarabine 500mg
Main

Cell Formulas
Range	Formula
D2:D12	D2	=VLOOKUP(C2&"*",$A$2:$A$15,1,0)

 

[ravi2628]

Is this what you want?

Fluff.xlsm
	A	B	C	D
1
2	Propofol Injection 200mg		Propofol	Propofol Injection 200mg
3	Enoxaparin Sodium 60mg		Enoxaparin Sodium	Enoxaparin Sodium 60mg
4	Heparin Sodium 25000IU		Heparin	Heparin Sodium 25000IU
5	Enoxaparin Sodium 40mg		Enoxaparin Sodium	Enoxaparin Sodium 60mg
6	Polymyxin B Sulphate 500000 IU		Polymyxin B Sulphate	Polymyxin B Sulphate 500000 IU
7	Sterile Noradrenaline Conc Inj 20mg		Sterile Noradrenaline Conc inj	Sterile Noradrenaline Conc Inj 20mg
8	Vancomycin Hcl 500mg		Vancomycin Hcl	Vancomycin Hcl 500mg
9	Atracurium Besylate 50mg		Atracurium Besylate	Atracurium Besylate 50mg
10	Omeprazole Injection 40mg		Omeprazole Injection	Omeprazole Injection 40mg
11	Sterile Noradrenaline Conc Inj 8mg		Sterile Noradrenaline Conc	Sterile Noradrenaline Conc Inj 20mg
12	L-Asparaginase 10000IU		Doxorubicin Hcl	Doxorubicin Hcl 50mg
13	Oxaliplatin 100mg
14	Doxorubicin Hcl 50mg
15	Cytarabine 500mg
Main

Cell Formulas
Range	Formula
D2:D12	D2	=VLOOKUP(C2&"*",$A$2:$A$15,1,0)

Hi fluff,

but I need the mapping in sheet1 with the mapping sheet.

sorry for the inconvenience as I was not specified earlier.

 

[Fluff]

In that case I have no idea what you are trying to do.
You will need to explain exactly what you are trying to do.

 

[fjns]

Hi, see the linked file for a possible solution...
Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table...
=IFERROR(TEXTJOIN(", ",,FILTER(Medicines!A:A,LEFT(Medicines!A:A,LEN(A2))=A2)),"")

Medicines.xlsx

 

[fjns]

Hi, I expanded the table with 3 columns.
(Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.)

The new formulas used in the new table...
B2: =IFERROR(IF(A2="","",TEXTJOIN(", ",,FILTER(Medicines!A:A,LEFT(Medicines!A:A,LEN(A2))=A2))),"")
C2: =IFERROR(IF(A2="","",COUNTA(FILTER(Medicines!A:A,LEFT(Medicines!A:A,LEN(A2))=A2))),"")
E2: =IFERROR(IF(OR(A2="",D2=0),"",INDEX(FILTER(Medicines!A:A,LEFT(Medicines!A:A,LEN(A2))=A2),D2)),"")

Medicines2.xlsx