How to use VLOOKUP function in MS Excel

0 votes

I have two sheets Sheet 1

Country PMU             Cluster
A       Asia            Mercury
B       Australia       Venus
C       North America   Jupiter

All the countries and continents are unique here In sheet 2 I have

CountryCode Country  PMU  Cluster
123         A
234         A
453         B
235         C

One country may have several codes. The PMU and Cluster must be combined with Sheet 2; Sheet 2 will include a new column for the country code. Any assistance is greatly appreciated.

Nov 13 in Others by Kithuzzz
• 20,660 points
31 views

1 answer to this question.

0 votes

Here is the formula for cell G2:

    ==VLOOKUP($F2,$A:$C,2,FALSE)

Here is the formula for cell H2:

    =VLOOKUP($F2,$A:$C,3,FALSE)

You can now drag your formulas down to finish. Vlookup formulae are really helpful, and I suggest researching how they work because someone else might be able to describe it better than I can. In essence, you are looking up a value (column F) in an array (columns A, B, C), and if a match is found, you return a column index (B = 2, C = 3, etc.). Last but not least, you are searching for a close (TRUE) or exact (FALSE) match. Apply FALSE almost always.

Additionally, research cell references and how to lock them (including the various $ sign rules). By maintaining the same lookup value and array, you may quickly drag formulas across the page.

answered Nov 13 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to open .xlsx files in MS Excel from VS Code?

Hello, to open xlxs files, or files ...READ MORE

answered Feb 17 in Others by gaurav
• 22,040 points
251 views
0 votes
1 answer
0 votes
1 answer

How to use VBA in Excel for Google Search?

Try this: Private Const LicenseRegistration As String = ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
132 views
0 votes
1 answer

Excel stock and sales data management

you must attach the event handler each ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
45 views
0 votes
1 answer

Using Excel VLOOKUP() function across two sheets

The syntax for VLOOKUP is VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup) OR, to start in ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
65 views
0 votes
1 answer

Way to overcome Excel Vlookup function limit of 256 characters

If you are using VLOOKUP like this: =VLOOKUP(A2,D2:Z10,3,FALSE) i.e. ...READ MORE

answered Sep 30 in Others by narikkadan
• 37,660 points
141 views
0 votes
1 answer

Convert three letter country codes to full country names

Just create a list to be used in ...READ MORE

answered Oct 16 in Others by narikkadan
• 37,660 points
49 views
0 votes
1 answer

How to use the INDIRECT function in Excel

Try this: =INDEX(INDIRECT($A$1&"!$1:$1048576"),MATCH($B$1,INDI ...READ MORE

answered Sep 29 in Others by narikkadan
• 37,660 points
74 views
0 votes
1 answer

How to use goal seek function in Excel user function?

In the Microsoft Excel Object-Sheet1(Sheet1), we can ...READ MORE

answered Oct 28 in Others by narikkadan
• 37,660 points
54 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP