Looking value in another sheet and returns the ID

[swamisnawale]

Hi All,

I have excel file in that i have 2 sheets S1 (orange) & S2 (blue). I have CID column in S2 and I want to check D column value of S2 in C column of S1 and then i want to return the ID in CID column

for example, I have 5 entries in S2 sheet

BadakhshanAfghanistan

BadakhshanAfghanistan

BadakhshanAfghanistan

BadakhshanAfghanistan

BadakhshanAfghanistan

BadakhshanAfghanistan

and in S1 i have this 

CONCAT2ID

BadakhshanAfghanistan

1

So I want the result in S2 to be

CONCAT1                                                                              CID

BadakhshanAfghanistan	1
BadakhshanAfghanistan	1
BadakhshanAfghanistan	1
BadakhshanAfghanistan	1
BadakhshanAfghanistan	1
BadakhshanAfghanistan	1

i have added excel sheet as well for your reference. Thanks :)

[#2 · Accepted solution]

@swamisnawale 

=VLOOKUP([@CONCAT1],Table2[[CONCAT2]:[ID]],2,FALSE)