Question related to vlookup multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2241873%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20vlookup%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2241873%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20have%20done%20VLOOKUP%20function.%3C%2FP%3E%3CP%3ELike%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2327).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F267830i4E24F23E93B671AA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2327).png%22%20alt%3D%22Screenshot%20(2327).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20i%20want%20solution%20in%26nbsp%3B%20Code%20with%20Country.%26nbsp%3B%3C%2FP%3E%3CP%3ELike%20-%20I%20should%20be%20come%26nbsp%3B%3CSTRONG%3E%5B100%5DIndia%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ESo%2C%20what%20formula%20should%20i%20put%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20Help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20attached%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2241873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2242072%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20vlookup%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20B2%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%22%5B%22%26amp%3B%24A2%26amp%3B%22%5D%22%26amp%3BVLOOKUP(%24A2%2C'Main%20Sheet'!%24A%241%3A%24B%245%2C2%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2242091%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20vlookup%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242091%22%20slang%3D%22en-US%22%3Eit%20worked!%20Thank%20you%20so%20much%20sir%3Asmiling_face_with_smiling_eyes%3A%3Asmiling_face_with_smiling_eyes%3A%3CBR%20%2F%3E%3CBR%20%2F%3E1st%20question%20---%20I%20do%20not%20understand%20that%20why%20we%20put%20%22%5B%22%26amp%3B%24A2%26amp%3B%22%5D%22%26amp%3B%20.Please%20explain%3F%3CBR%20%2F%3E%3CBR%20%2F%3E2nd%20question%20---%20Sir%2C%20i%20want%20to%20understand%20full%20details%20of%20wildcards%20like%20-%3CBR%20%2F%3E%22%20%22%3CBR%20%2F%3E--%3CBR%20%2F%3E%3F%3CBR%20%2F%3E%26amp%3B%3CBR%20%2F%3EAnd%20so%20on.%3CBR%20%2F%3ECan%20you%20please%20share%20a%20details%20or%20Link%20of%20wildcards%20that%20i%20will%20learn%20%3F%20Please%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2242109%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20vlookup%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242109%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EQuotes%20%22%20are%20used%20around%20fixed%20text%20values%20in%20a%20formula.%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20%3D%22Zan%22%20returns%20the%20text%20%3CSTRONG%3EZan%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3E%22%22%20is%20an%20empty%20string%20(text%20value)%2C%20and%20%22%20%22%20is%20a%20string%20consisting%20of%20a%20space.%3C%2FP%3E%0A%3CP%3E%26amp%3B%20is%20the%20concatenation%20operator.%20For%20example%2C%20%22Zan%22%20%26amp%3B%20%22%20%22%20%26amp%3B%20%22Hanifee%22%20returns%20%3CSTRONG%3EZan%20Hanifee%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EYou%20wanted%20to%20concatenate%20the%20code%20enclosed%20in%20%5B%20%5D%20with%20the%20country.%3C%2FP%3E%0A%3CP%3ETo%20get%20the%20code%20enclosed%20in%20%5B%20%5D%20we%20use%20%22%5B%22%20%26amp%3B%20A2%20%26amp%3B%20%22%5D%22%3C%2FP%3E%0A%3CP%3EWe%20concatenate%20this%20with%20the%20result%20of%20VLOOKUP%3A%3C%2FP%3E%0A%3CP%3E%3D%22%5B%22%20%26amp%3B%20A2%20%26amp%3B%22%5D%22%20%26amp%3B%20VLOOKUP(...)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2242131%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20vlookup%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2242131%22%20slang%3D%22en-US%22%3EI%20understood.%20Thank%20you%20very%20much%20sir%3Asmiling_face_with_smiling_eyes%3A%3Asmiling_face_with_smiling_eyes%3A%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

I have done VLOOKUP function.

Like - 

Screenshot (2327).png

 

But i want solution in  Code with Country. 

Like - I should be come [100]India

So, what formula should i put?

 

Please Help?

 

Here is an attached file

 

 

10 Replies

@Zan_Hanifee 

In B2:

="["&$A2&"]"&VLOOKUP($A2,'Main Sheet'!$A$1:$B$5,2,FALSE)

Fill down.

it worked! Thank you so much sir

1st question --- I do not understand that why we put "["&$A2&"]"& .Please explain?

2nd question --- Sir, i want to understand full details of wildcards like -
" "
--
?
&
And so on.
Can you please share a details or Link of wildcards that i will learn ? Please

@Zan_Hanifee 

Quotes " are used around fixed text values in a formula.

For example, ="Zan" returns the text Zan.

"" is an empty string (text value), and " " is a string consisting of a space.

& is the concatenation operator. For example, "Zan" & " " & "Hanifee" returns Zan Hanifee.

You wanted to concatenate the code enclosed in [ ] with the country.

To get the code enclosed in [ ] we use "[" & A2 & "]"

We concatenate this with the result of VLOOKUP:

="[" & A2 &"]" & VLOOKUP(...)

I understood. Thank you very much sir

@Hans Vogelaar 

Hello Sir

In my DATA sheet, there are 3 persons with one Emp ID,s

Like - 

Screenshot (2384).png

 

So, i want to match here. what formula should i write with the help of INDEX with MACTCH function in here - 

Screenshot (2386).png

 

And with the help of VLOOKUP function - 

Screenshot (2387).png

 

In simple way to say that - What formula should i write -

only VLOOKUP function(With VLOOKUP sheet)

AND 

only INDEX WITH MATCH function(With Index with Match sheet)... Both..

 

Without creating a Helper Column.

 

Please Help.

 

Here is an attached file.

 

 

 

 

 

 

 

 

 

 

 

 

 

@Zan_Hanifee 

The attached version shows a way to do it using INDEX, SMALL and COUNTIF.

I don't know of a way to do it using VLOOKUP without a helper column.

@Hans Vogelaar 

Thank you so much sir

 

1st question -  Sir, please let me confirm that do you use Ctrl + Shift + Enter in the formula in this attached file?

 

2nd question - I have done VLOOKUP function with help of  Helper column. So, please can you tell me that is this correct or not?

 

Here is a attached file

@Zan_Hanifee 

Thank you so much sir

 

1st question -  Sir, please let me confirm that do you use Ctrl + Shift + Enter in the formula in this attached file?

 

2nd question - I have done VLOOKUP function with help of  Helper column. So, please can you tell me that is this correct or not?

 

Here is a attached file

@Zan_Hanifee 

1) Yes - if you look at the formula bar, you'll see { } around the formulas on the 'With INDEX and MATCH formula' sheet; this indicates that the formulas are array formulas confirmed with Ctrl+Shift+Enter.

 

2) Yes, that is the way to do it with a helper column.

Ok..
Thank you so much sir