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, 2022 in Others by Kithuzzz
• 38,010 points
294 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, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,420 points
637 views
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, 2022 in Others by gaurav
• 23,260 points
2,995 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
3,047 views
0 votes
1 answer

Excel stock and sales data management

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

answered Sep 23, 2022 in Others by narikkadan
• 63,420 points
312 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, 2022 in Others by narikkadan
• 63,420 points
744 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, 2022 in Others by narikkadan
• 63,420 points
2,871 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, 2022 in Others by narikkadan
• 63,420 points
540 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, 2022 in Others by narikkadan
• 63,420 points
597 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, 2022 in Others by narikkadan
• 63,420 points
545 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