CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Alkippe Nikephoros
Alkippe Nikephoros

Reputation: 151

Excel Formula - Get Value From Another Excel Sheet Based on Column in Current Sheet

I have two sheets in excel, Summary and Data. The data sheet is connecting through SQL Server and Summary sheet is summarising the data in a table.

Summary sheet 

+------+--------------+
| ID   | Month - Year | 
+------+--------------+
|    1 |              |
|    2 |              |     
|    3 |              |  
|    4 |              |   
|    5 |              |
+------+--------------+

Note: I am using a formula to get the distinct IDs from the data sheet. ID column is column A, Month - Year column is column B. ID 1 is in A2 square.

Data sheet 

+------+--------------+
| ID   | Month - Year | 
+------+--------------+
|    1 |  Oct 2014    |
|    2 |  Dec 2014    |    
|    3 |  Oct 2016    | 
|    4 |  Jan 2016    |  
|    5 |  Nov 2015    |
|    6 |  Jul 2015    |  
|    7 |  Jan 2016    |  
|    8 |  Nov 2015    |
|    7 |  Jan 2016    |  
|    8 |  Nov 2015    |
+------+--------------+

Note: ID 1 is in A2 square, Month - Year is in B2 square.

How do I write a formula for the Summary Month - Year column to get each month - year (from data sheet) based on the ID column (in the summary sheet)?

Expected Result:

+------+--------------+
| ID   | Month - Year | 
+------+--------------+
|    1 |  Oct 2014    |
|    2 |  Dec 2014    |    
|    3 |  Oct 2016    | 
|    4 |  Jan 2016    |  
|    5 |  Nov 2015    |
|    6 |  Jul 2015    |  
|    7 |  Jan 2016    |  
|    8 |  Nov 2015    |
+------+--------------+

Equivalent Expected Result in Pivot Table (with rows Month-Year and ID):

-Nov 2015
--5
--8
-Jan 2016
--4
--7
-Jul 2015
--6
-Oct 2014
--1
-Dec 2014
--2
-Oct 2016
--3

Effort: =VLOOKUP(A2,Data!$A$2:$A$500,2,FALSE)

Upvotes: 0

Views: 9295

Answers (1)

Bullfrog
Bullfrog

Reputation: 229

=VLOOKUP(A2,Data!$A$2:$B$500,2,FALSE)

The formula you tried needs to include both the columns in the table array variable.

Additionally the array must contain the lookup value in the first column of the table array.

I prefer however to use the INDEX MATCH combination, it removes the constraint of the search column being the most right column of the table array. Using the values you gave for your ranges it would be:

=INDEX(Data!$B$2:$B$500,MATCH(A2,Data!$A$2:$A$500,0))

For more information about VLOOKUP you can get info at: https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

Upvotes: 3

Related Questions