by The tutorial shows how to use the VLOOKUP function to copy data from another worksheet or workbook, Vlookup in multiple sheets, and look up dynamically to return values from different sheets into different cells.
When looking up some information in Excel, it's a rare case when all the data is on the same sheet. More often, you will have to search across multiple sheets or even different workbooks. The good news is that Microsoft Excel provides more than one way to do this, and the bad news is that all the ways are a bit more complicated than a standard VLOOKUP formula. But with just a little patience, we will figure them out :)
How to VLOOKUP between two sheets
For starters, let's investigate a simplest case - using VLOOKUP to copy data from another worksheet. It's very similar to a regular VLOOKUP formula that searches on the same worksheet. The difference is that you include the sheet name in the table_array argument to tell your formula in which worksheet the lookup range is located.
The generic formula to VLOOKUP from another sheet is as follows:
As an example, let's pull the sales figures from Jan report to Summary sheet. For this, we define the following arguments:
- Lookup_values are in column A on the Summary sheet, and we refer to the first data cell, which is A2.
- Table_array is the range A2:B6 on the Jan sheet. To refer to it, prefix the range reference with the sheet name followed by the exclamation point: Jan!$A$2:$B$6.
Please pay attention that we lock the range with absolute cell references to prevent it from changing when copying the formula to other cells.
Col_index_num is 2 because we want to copy a value from column B, which is the 2nd column in the table array. - Range_lookup is set to FALSE to look up an exact match.
Putting the arguments together, we get this formula:
=VLOOKUP(A2, Jan!$A$2:$B$6, 2, FALSE)
Drag the formula down the column and you will get this result:
In a similar manner, you can Vlookup data from the Feb and Mar sheets:
=VLOOKUP(A2, Feb!$A$2:$B$6, 2, FALSE)
=VLOOKUP(A2, Mar!$A$2:$B$6, 2, FALSE)
Tips and notes:
- If the sheet name contains spaces or non-alphabetical characters, it must be enclosed in single quotation marks, like 'Jan Sales'!$A$2:$B$6. For more info, please see How to reference another sheet in Excel.
- Instead of typing a sheet name directly in a formula, you can switch to the lookup worksheet and select the range there. Excel will insert a reference with the correct syntax automatically, sparing you the trouble to check the name and troubleshoot.
Vlookup from a different workbook
To VLOOKUP between two workbooks, include the file name in square brackets, followed by the sheet name and the exclamation point.
For example, to search for A2 value in the range A2:B6 on Jan sheet in the Sales_reports.xlsx workbook, use this formula:
=VLOOKUP(A2, [Sales_reports.xlsx]Jan!$A$2:$B$6, 2, FALSE)
For full details, please see VLOOKUP from another workbook in Excel.
Vlookup across multiple sheets with IFERROR
When you need to look up between more than two sheets, the easiest solution is to use VLOOKUP in combination with IFERROR. The idea is to nest several IFERROR functions to check multiple worksheets one by one: if the first VLOOKUP does not find a match on the first sheet, search in the next sheet, and so on.
To see how this approach works on real-life data, let's consider the following example. Below is the Summary table that we want to populate with the item names and amounts by looking up the order number in West and East sheets:
First, we are going to pull the items. For this, we instruct the VLOOKUP formula to search for the order number in A2 on the East sheet and return the value from column B (2nd column in table_array A2:C6). If an exact match is not found, then search in the West sheet. If both Vlookups fail, return "Not found".
=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 2, FALSE), "Not found"))
To return the amount, simply change the column index number to 3:
=IFERROR(VLOOKUP(A2, East!$A$2:$C$6, 3, FALSE), IFERROR(VLOOKUP(A2, West!$A$2:$C$6, 3, FALSE), "Not found"))
Tip. If needed, you can specify different table arrays for different VLOOKUP functions. In this example, both lookup sheets have the same number of rows (A2:C6), but your worksheets may be different in size.
Vlookup in multiple workbooks
To Vlookup between two or more workbooks, enclose the workbook name in square brackets and put it before the sheet name. For example, here's how you can Vlookup in two different files (Book1 and Book2) with a single formula:
=IFERROR(VLOOKUP(A2, [Book1.xlsx]East!$A$2:$C$6, 2, FALSE), IFERROR(VLOOKUP(A2, [Book2.xlsx]West!$A$2:$C$6, 2, FALSE),"Not found"))
Make column index number dynamic to Vlookup multiple columns
In situation when you need to return data from several columns, making col_index_num dynamic could save you some time. There are a couple of adjustments to be made:
- For the col_index_num argument, use the COLUMNS function that returns the number of columns in a specified array: COLUMNS($A$1:B$1). (The row coordinate does not really matter, it can be just any row.)
- In the lookup_value argument, lock the column reference with the $ sign ($A2), so it remains fixed when copying the formula to other columns.
As the result, you get a kind of dynamic formula that extracts matching values from different columns, depending on which column the formula is copied to:
=IFERROR(VLOOKUP($A2, East!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), IFERROR(VLOOKUP($A2, West!$A$2:$C$6, COLUMNS($A$1:B$1), FALSE), "Not found"))
When entered in column B, COLUMNS($A$1:B$1) evaluates to 2 telling VLOOKUP to return a value from the 2nd column in the table array.
When copied to column C (i.e. you've dragged the formula from B2 to C2), B$1 changes to C$1 because the column reference is relative. Consequently, COLUMNS($A$1:C$1) evaluates to 3 forcing VLOOKUP to return a value from the 3rd column.
This formula works great for 2 - 3 lookup sheets. If you have more, repetitive IFERRORs become too cumbersome. The next example demonstrates a bit more complicated but a lot more elegant approach.
Vlookup multiple sheets with INDIRECT
One more way to Vlookup between multiple sheets in Excel is to use a combination of VLOOKUP and INDIRECT functions. This method requires a little preparation, but in the end, you will have a more compact formula to Vlookup in any number of spreadsheets.
A generic formula to Vlookup across sheets is as follows:
Where:
- Lookup_sheets - a named range consisting of the lookup sheet names.
- Lookup_value - the value to search for.
- Lookup_range - the column range in the lookup sheets where to search for the lookup value.
- Table_array - the data range in the lookup sheets.
- Col_index_num - the number of the column in the table array from which to return a value.
For the formula to work correctly, please bear in mind the following caveats:
- It's an array formula, which must be completed by pressing Ctrl + Shift + Enter keys together.
- All the sheets must have the same order of columns.
- As we use one table array for all lookup sheets, specify the largest range if your sheets have different numbers of rows.
How to use the formula to Vlookup across sheets
To Vlookup multiple sheets at a time, carry out these steps:
- Write down all the lookup sheet names somewhere in your workbook and name that range (Lookup_sheets in our case).
- Adjust the generic formula for your data. In this example, we'll be:
- searching for A2 value (lookup_value)
- in the range A2:A6 (lookup_range) in four worksheets (East, North, South and West), and
- pull matching values from column B, which is column 2 (col_index_num) in the data range A2:C6 (table_array).
With the above arguments, the formula takes this shape:
=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), 2, FALSE)Please notice that we lock both ranges ($A$2:$A$6 and $A$2:$C$6) with absolute cell references.
- Enter the formula in the topmost cell (B2 in this example) and press Ctrl + Shift + Enter to complete it.
- Double click or drag the fill handle to copy the formula down the column.
As the result, we've got the formula to look up the order number in 4 sheets and retrieve the corresponding item. If a specific order number is not found, a #N/A error is displayed like in row 14:
To return the amount, simply replace 2 with 3 in the col_index_num argument as amounts are in the 3rd column of the table array:
=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$C$6"), 3, FALSE)
If you'd like to replace the standard #N/A error notation with your own text, wrap the formula into the IFNA function:
=IFNA(VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$C$6"), 3, FALSE), "Not found")
Vlookup multiple sheets between workbooks
This generic formula (or its any variation) can also be used to Vlookup multiple sheets in a different workbook. For this, concatenate the workbook name inside INDIRECT like shown in the below formula:
=IFNA(VLOOKUP($A2, INDIRECT("'[Book1.xlsx]" & INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'[Book1.xlsx]" & Lookup_sheets & "'!$A$2:$A$6"), $A2)>0), 0)) & "'!$A$2:$C$6"), 2, FALSE), "Not found")
Vlookup between sheets and return multiple columns
If you want to pull data from several columns, a multi-cell array formula can do that in one go. To create such a formula, supply an array constant for the col_index_num argument.
In this example, we wish to return the item names (column B) and amounts (column C), which are the 2nd and 3rd columns in the table array, respectively. So, the required array is {2,3}.
=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets &"'!$A$2:$C$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), {2,3}, FALSE)
To correctly enter the formula in multiple cells, this is what you need to do:
- In the first row, select all the cells to be populated (B2:C2 in our example).
- Type the formula and press Ctrl + Shift + Enter. This enters the same formula in the selected cells, which will return a different value in each column.
- Drag down the formula to the remaining rows.
How this formula works
To better understand the logic, let's break down this basic formula to the individual functions:
=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$6"), $A2)>0), 0)) &"'!$A$2:$C$6"), 2, FALSE)
Working from the inside out, here's what the formula does:
COUNTIF and INDIRECT
In a nutshell, INDIRECT builds the references for all lookup sheets, and COUNTIF counts the occurrences of the lookup value (A2) in each sheet:
--(COUNTIF( INDIRECT("'"&Lookup_sheets&"'!$A$2:$A$6"), $A2)>0)
In more detail:
First, you concatenate the range name (Lookup_sheets) and the range reference ($A$2:$A$6), adding apostrophes and the exclamation point in the right places to make an external reference, and feed the resulting text string to the INDIRECT function to dynamically refer to the lookup sheets:
INDIRECT({"'East'!$A$2:$A$6"; "'South'!$A$2:$A$6"; "'North'!$A$2:$A$6"; "'West'!$A$2:$A$6"})
COUNTIF checks each cell in the range A2:A6 on each lookup sheet against the value in A2 on the main sheet and returns the count of matches for each sheet. In our dataset, the order number in A2 (101) is found in the West sheet, which is 4th in the named range, so COUNTIF returns this array:
{0;0;0;1}
Next, you compare each element of the above array with 0:
--({0; 0; 0; 1}>0)
This yields an array of TRUE (greater than 0) and FALSE (equal to 0) values, which you coerce to 1's and 0's by using a double unary (--), and get the following array as the result:
{0; 0; 0; 1}
This operation is an extra precaution to handle a situation when a lookup sheet contains several occurrences of the lookup value, in which case COUNTIF would return a count greater than 1, while we want only 1's and 0's in the final array (in a moment, you will understand why).
After all these transformations, our formula looks as follows:
VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0)) &"'!$A$2:$C$6"), 2, FALSE)
INDEX and MATCH
At this point, a classic INDEX MATCH combination steps in:
INDEX(Lookup_sheets, MATCH(1, {0;0;0;1}, 0))
The MATCH function configured for exact match (0 in the last argument) looks for the value 1 in the array {0;0;0;1} and returns its position, which is 4:
INDEX(Lookup_sheets, 4)
The INDEX function uses the number returned by MATCH as the row number argument (row_num), and returns the 4th value in the named range Lookup_sheets, which is West.
So, the formula further reduces to:
VLOOKUP($A2, INDIRECT("'"&"West"&"'!$A$2:$C$6"), 2, FALSE)
VLOOKUP and INDIRECT
The INDIRECT function processes the text string inside it:
INDIRECT("'"&"West"&"'!$A$2:$C$6")
And converts it into a reference that goes to the table_array argument of VLOOKUP:
VLOOKUP($A2, 'West'!$A$2:$C$6, 2, FALSE)
Finally, this very standard VLOOKUP formula searches for the A2 value in the first column of the range A2:C6 on the West sheet and returns a match from the 2nd column. That's it!
Dynamic VLOOKUP to return data from multiple sheets into different cells
First off, let's define what exactly the word "dynamic" means in this context and how this formula is going to be different from the previous ones.
In case you have large chunks of data in the same format that are split over multiple spreadsheets, you may want to extract information from different sheets into different cells. The image below illustrates the concept:
Unlike the previous formulas that retrieved a value from a specific sheet based on a unique identifier, this time we are looking to extract values from several sheets at a time.
There are two different solutions for this task. In both cases, you need to do a little preparatory work and create named ranges for data cells in each lookup sheet. For this example, we defined the following ranges:
- East_Sales - A2:B6 on the East sheet
- North_Sales - A2:B6 on the North sheet
- South_Sales - A2:B6 on the South sheet
- West_Sales - A2:B6 on the West sheet
VLOOKUP and nested IFs
If you have a reasonable number of sheets to look up, you can use nested IF functions to select the sheet based on the keywords in the predefined cells (cells B1 through D1 in our case).
With the lookup value in A2, the formula is follows:
=VLOOKUP($A2, IF(B$1="east", East_Sales, IF(B$1="north", North_Sales, IF(B$1="south", South_Sales, IF(B$1="west", West_Sales)))), 2, FALSE)
Translated into English, the IF part reads:
If B1 is East, look in the range named East_Sales; if B1 is North, look in the range named North_Sales; if B1 is South, look in the range named South_Sales; and if B1 is West, look in the range named West_Sales.
The range returned by IF goes to table_array of VLOOKUP, which pulls a matching value from the 2nd column on the corresponding sheet.
The clever use of mixed references for the lookup value ($A2 - absolute column and relative row) and the logical test of IF (B$1 - relative column and absolute row) allows copying the formula to other cells without any changes - Excel adjusts the references automatically based on the relative position of a row and column.
So, we enter the formula in B2, copy it right and down to as many columns and rows as needed, and get the following result:
INDIRECT VLOOKUP
When working with many sheets, multiple nested levels could make the formula too lengthy and difficult to read. A far better way is to create a dynamic vlookup range with the help of INDIRECT:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Here, we concatenate the reference to the cell that contains a unique part of the named range (B1) and the common part (_Sales). This produces a text string like "East_Sales", which INDIRECT converts to the range name understandable by Excel.
As the result, you get a compact formula that works beautifully on any number of sheets:
That's how to Vlookup between sheets and files in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Vlookup multiple sheets examples (.xlsx file)
191 comments
Hi,
I am trying to use the formula under 'Lookup multiple sheets between workbooks'
The spreadsheet I am using will be opened by multiple colleagues across the business but the workbook it is linking to will not be open. Therefore using the COUNTIF function is returning #N/A:
=(VLOOKUP($AH8, INDIRECT("'[Book1.xlsx]"&INDEX(Sheet_list, MATCH(1, --(COUNTIF(INDIRECT("'[Book1.xlsx]"&Sheet_list&"'!$B$17:$B$32"), $AH8)>0), 0))&"'!$B$17:$K$32"), 4, FALSE))
Is there a way to fix this so that it works when Book1 is not open?
Thanks
Hi!
A VBA macro or Power Query can work with a workbook that is not open. Regular Excel formulas require special references. Read more - External reference to a closed workbook.
Hi,
i need to be able to use VLOOKUP on my current worksheet (sheet1), but compare values in sheet2 to sheet1. I currently use VLOOKUP('SHEET2'!A1, 'SHEET1'!$A$1;$A$100,1,0) but i am getting incorrect values possibly due to 1,0 (index and range). How do i fix the formula?
I need to stay in sheet1 and cannot use vlookup in sheet2.
Thank You
Maksim
Hello!
The information you provided is not enough to understand your case and give you any advice.
Explain what values you are getting and what you would like to get.
Hi, great tutorial on Vlookup. First time user of Vlookup. My first formula worked fine; =VLOOKUP(D4,CREWS2!A1:E55,2). The second formula; =VLOOKUP(C8,CREWS2!$A$1:$E$55,1)is producing an odd result. Both formulas use the same data table;
A B C D
GRF Groundperson 6 Months - ST $60.72 ST
GRG Groundperson 6 Months - PT $88.05 PT
JTA Journeyman Tree Trimmer - ST $95.68 ST
JTB Journeyman Tree Trimmer - PT $138.74 PT
the first formula finds column A when the name in column B is entered. my second formula is to find column B when the name in column A is entered. When i run second formula I am getting GRG when i enter Journeyman Tree Trimmer - ST, instead of JTA.
Does the direction column B to column A matter? Does the name structure matter; Journeyman Tree Trimmer - ST?
Thank you for any assistance
Hello!
The VLOOKUP function always searches in the leftmost column of the table and returns a value from a column to the right. To get the value to the left of the lookup column, use the INDEX + MATCH combination.
I hope I answered your question. If something is still unclear, please feel free to ask.
Trying to make the vlookup indirect function work for my data and can't quite get my head around it.
What I have based on your examples above is:
=IFNA(VLOOKUP($B2, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$C$2:$C$20"), $B2)>0), 0)) & "'!$A$2:$R$20"), 18, FALSE), "Not found")
B2 = lookup value
C2:C20 across the multiple sheets is range the lookup value will be in
A2:R20 is my table/data
Column 18 or R is where i want to return the value from that matches the C2 result.
The only thing being returned no matter what i enter in B2 is "Not found""/NA. Any help is much appreciated.
I'm trying to search for a customer number (B2) that returns text/notes from column R/18 across multiple worksheets and the text is not the same across each occurrence. Is this possible?
Hello!
I cannot verify your formula as I do not have your data. Use the instructions carefully. Check the named range Lookup_sheets. In it, you need to write down the names of the worksheets.
Multiple sheets linked to one sheet, sample if i have 4 sheets of my inventories and i want to monitor all damages in fifth sheets.
Hello!
Use external references in formulas. All other recommendations can be found in this article.
Como fazer para encontrar o último valor pago da plan 01 e trazer para plan 02
=VLOOKUP(A2, Sheet3!E:I, 5, FALSE)
Fiz uma formula com Vlookup para buscar o ultimo valor pago de determinado item da planilha 01 para a planilha 02. (=VLOOKUP(A2, Sheet3!E:I, 5, FALSE), porém o retorno que aparece é só do primeiro valor pago.
Como fazer para aparecer o último valor pago?
Hello!
To find the last match, use the XLOOKUP function.
Hi
I need to use VLOOKUP function for searching a value in 250 sheets of a different workbook. What formula better to use?
Thank you
Hi!
This article has examples of VLOOKUP on 2 sheets in 2 books. Add another 248 sheets to them.
Hi, I have been working on a formula for days and I need help. Sheet 1 Cell E2, I need a return value from based on what is in Sheet 2 Cell I2
IN E2, I would like the value "SWM00009" if Sheet 3 cell I2 equals one of 2063, 2076, 2088, 2098, 2109, 2118, 2121, 2125, 2127, 2132, 2135, 2148, 2152
"SWM00002" if Sheet 3 cell I2 equals one of 2010, 2039, 2053, 2150, 2154, 2591, 2629
"SWM00004" if Sheet 3 Cell I2 equals one of 2668, 2687, 2840, 2878, 2903, 2989, 3001,3002
I need multiple conditions and return values. HELP! Thanks in advance.
Hello!
Use nested IF statements:
=IF(SUM(--(I2={2063,2076,2088,2098,2109,2118,2121,2125,2127,2132,2135,2148,2152}))>0,"SWM00009",IF(SUM(--(I2={2010,2039,2053,2150,2154,2591,2629}))>0,"SWM00002", IF(SUM(--(I2={2668,2687,2840,2878,2903,2989,3001,3002}))>0,"SWM00004","")))
This should solve your task.
Hi there,
I desperately need help with a formula. This site is excellent I just can't make the formula work for my situation, I'm due to go on maternity leave in a few weeks and need to sort out the following formula for my replacement.
In worksheet 1 - I have employee numbers which I am able to use VLOOKUP to match to ascertain if the employee is listed in worksheet 2 long term sick tracker.
FORMULA USED=IF(VLOOKUP(C3,'3.Long term sick Tracker'!$A$1:$A$10044,1,FALSE)=0,"No","Yes")
The problem is I need to enter a new criteria to detect in worksheet 2 long term sick tracker- if the employee is active - column M.
Can anyone help me. I've never asked for excel help before, but desperately need it.
Thanks
Nicky
Hello!
Here is the article that may be helpful to you: How to use VLOOKUP in Excel: advanced formula examples.
See paragraph "Formula 2. Excel VLOOKUP with multiple conditions".
I hope I answered your question. If you have any other questions, please don’t hesitate to ask.
Hello!
I am using the below formula in Google sheets, I noticed it functions in Excel but does not return the values in Google Sheets. Is there a different way this needs to be formatted to operate in google sheets? I noticed in one of the comments Import range is mentioned as a fix for google sheets. Is that what would need to be edited? if so where in the formula would this need to be added?
=ArrayFormula(VLOOKUP($A2, INDIRECT("'"&INDEX(LookupSheets, MATCH(1, --(COUNTIF(INDIRECT("'"& LookupSheets&"'!$A$2:$A$30"), $A2)>0), 0)) &"'!$A$2:$L$30"), 12, FALSE))
Hello Elizabeth,
If you use Google Sheets, please look through these blog posts, you may find the answers there:
Google Sheets VLOOKUP with examples
INDEX MATCH in Google Sheets – flexible Vlookup for your spreadsheets
I want use multiple lookup formula
Date
Shift
Lookup value
Lookup array
This is data range i want look up lookup value with match with date and shift
HI all
I need two table array lookups (each in a different worksheet), I've tried this to no avail. I am sure I'm missing something very obvious:
=VLOOKUP($A1,IF($B1="criteria 1),'[criteria 1 filename.xlsx]list'!$1:$1048576,IF($B1="criteria 2",’[criteria 2 filename.xlsx]list'!$1:$1048576)))), 2, FALSE).
Many thanks.
HI all
I need two table array lookups (each in a different worksheet), I've tried this to no avail. I am sure I'm missing something very obvious:
=VLOOKUP($A1,IF($B1="criteria 1),'[criteria 1 filename.xlsx]list'!$1:$1048576,IF($B1="criteria 2",’[criteria 2 filename.xlsx]list'!$1:$1048576)))), 2, FALSE).
Many thanks.
Hi,
I used the IFERROR nested function but It says I used the maximum amount of IFERROR in one formula, but I still had more sheets in a workbook. What should I do?
Hi!
I can't see your formula, so I can only guess. Microsoft doesn't say anything about the IFERROR usage limit. For the IF function, the usage limit is 64. Your formula may have exceeded 255 characters.
Here is the article that may be helpful to you: Best practices for using IFERROR in Excel
i need vlookup formua for multiple sheets
sheet1 - data1(numbers)
sheet2- data price
sheet3- that data ids
sheet4- wana apply all these while taking data1 the remaining sheets also wanna visible on this
plz if you have this type formulae will you plz share that formula excel on my mail id
How do I use vlookup from 4 different tabs to pull data into Master tab in the same excel
Hi!
See above - VLOOKUP multiple sheets with INDIRECT
Dear. I am using this formula, it work for Excel with multiple sheet, however, it got some error in google sheet
=ARRAY_CONSTRAIN(ARRAYFORMULA(ROUNDUP(VLOOKUP($E11, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(1, --(COUNTIF(INDIRECT("'"& Lookup_sheets&"'!$A$2:$A$1000"), $E11)>0), 0)) &"'!$A$2:$P$1000"), 12, FALSE),3)), 1, 1)
The lookup_sheet (named ranged)
1. Kitchen
2. Show Kitchen
3. Bakery
from three above, excel work it and able to find the value. However, when perform in google sheet, the indirect unable to look for the value, the lookup value only able to find the first row of named ranged which is KITCHEN. May i know any solution on this?
Hello Reeve,
To get help with your formula, consider sharing an editable copy of your Google spreadsheet (not Excel) with us (support@apps4gs.com). To grant us access to your data, press the Share button at the upper right corner of your Google Sheets and enter support@apps4gs.com. Let us know where the formula is and we'll look into it.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
i have a query. i have a sheet with all the roll numbers and students of a class. i have another sheet which have five test marks of students only who have given exams. i have to now copy these marks in in front of respective roll numbers in first sheet. how can i do it. becausew in any second sheet many roll no. s entries are missing as they havent' given exam.
Hi!
Your description of the problem is not very clear. But I recommend reading this manual - Vlookup multiple matches in Excel with one or more criteria.
I am trying to combine a double Vlookup function with and IMPORTATRANGE function. Basically:
1)
I want to import earnings data from this sheet: https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0
To this other sheet: https://docs.google.com/spreadsheets/d/1ZD5yfybdogWJ0Bmb-7J9V_VJtkEoLPV8gPZwHwWr7es/edit#gid=0
To import datat I used the following formula: =Vlookup(B4, IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A1:D"),2,0)
2) This is where I get stuck...
I want to do a second vlookup that will in a way filter the data according to the view of the Month that I select in Cell B2 (January, March, or April). I don't want to add more columns, I just want columns B and C to display the data.
I figured using a double vlookup + an Importrange function would work, not sure if there is an easier way. Can you please help me?
Hello Jose,
I replaced the third argument of your VLOOKUP with the MATCH function to find the number of the column required based on B2 contents:
=Vlookup(B4,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A3:D"),MATCH($B$2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Le-ZyVh_2iS2ZqaCq9y5sCLPrH5aIMjx9fLtTWMK1lw/edit#gid=0","Earnings!A2:D2"),0),0)
The formula is also in your spreadsheet :)