# Question related to vlookup multiple criteria

Regular Contributor

# Question related to vlookup multiple criteria

Hello Everyone,

I have done VLOOKUP function.

Like -

But i want solution in  Code with Country.

Like - I should be come [100]India

So, what formula should i put?

Here is an attached file

10 Replies

# Re: Question related to vlookup multiple criteria

In B2:

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

Fill down.

# Re: Question related to vlookup multiple criteria

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.

# Re: Question related to vlookup multiple criteria

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(...)

# Re: Question related to vlookup multiple criteria

I understood. Thank you very much sir

# Re: Question related to vlookup multiple criteria

Hello Sir

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

Like -

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

And with the help of VLOOKUP function -

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.

Here is an attached file.

# Re: Question related to vlookup multiple criteria

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.

# Re: Question related to vlookup multiple criteria

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

# Re: Question related to vlookup multiple criteria

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

# Re: Question related to vlookup multiple criteria

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.

# Re: Question related to vlookup multiple criteria

Ok..
Thank you so much sir