Question related to vlookup multiple criteria

Iron 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

@Excel 

In B2:

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

Fill down.

it worked! Thank you so much sir:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

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

@Excel 

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:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes:

@Hans Vogelaar 

Hello Sir:smiling_face_with_smiling_eyes:

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.

 

 

 

 

 

 

 

 

 

 

 

 

 

@Excel 

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:smiling_face_with_smiling_eyes:

 

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

@Excel 

Thank you so much sir:smiling_face_with_smiling_eyes:

 

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

@Excel 

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:smiling_face_with_smiling_eyes::smiling_face_with_smiling_eyes: