Tips: To find exactly articles with useful content for readers, search on Google with the syntax: "Keyword" + "khoafastnews". (Example: new card for new priest Khoafastnews).Search now
20 lượt xem

how to let VLOOKUP across multiple sheets in Excel in two bodies attached to by examples – KHOA

thes tutorial shows how to let innate competences thes VLOOKUP use to copy data starting from second worksheet or workbook, Vlookup in multiple sheets, and design up dynamically to return values starting from not only at all along from with thes too sheets into not only at all along from with thes too cells.

later looking up some of news in Excel, news change technology’s a scarce situation later all thes data is on along from with thes too sheet. more often, player also continue along from with help to see out across multiple sheets or in spite of not only at all along from with thes too workbooks. thes many years of experience dew dew news is this Microsoft Excel provides more than one how to manufacture this feature thing, and thes talent is this all thes methods are a bit more complicated than a correction VLOOKUP formula. however in two bodies attached to by just have manufacture sometimes Patience, you also continue figure them outside 🙂

  • VLOOKUP between second worksheets
  • VLOOKUP between second workbooks
  • VLOOKUP across multiple sheets in two bodies attached to by IFERROR
  • VLOOKUP multiple sheets in two bodies attached to by INDIRECT
  • funny VLOOKUP to pull data starting from multiple sheets into not only at all along from with thes too cells

how to let VLOOKUP between second sheets

and only concepts starters, manufacture’s investigate a simplest situation – using VLOOKUP to copy data starting from second worksheet. news change technology’s extremely same as to a regular VLOOKUP formula this searches on along from with thes too worksheet. thes highlights is this player include thes sheet common insert name in every table_array argument to to understand deeply this don’t interrupt there your formula while worksheet thes lookup range is located.

thes generic formula to VLOOKUP starting from second sheet is as follows:

VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

As an if, manufacture’s pull thes sell products figures starting from Jan explain to Summary sheet. and only concepts this feature thing, you define thes following arguments:

  • Lookup_values are in column A on thes Summary sheet, and you talk still about thes biggest number 1 data cell, which is A2.
  • Table_array is thes range A2:B6 on thes Jan sheet. To refer to news change technology, prefix thes range reference in two bodies attached to by thes sheet common insert name followed manufacture thes exclamation point: Jan!$AUSD2:$BUSD6.

    Please pay attention this you lock thes range in two bodies attached to by extremely cell references to prevent news change technology starting from changing later copying thes formula to other cells.
    Col_index_num is 2 and to be you have problem needed to copy a value starting from column B, which is thes 2nd column in every table array.

  • Range_lookup is mix to FALSE to design up an accurate duplicate.

Putting thes arguments sitting with concepts each other, you possessed this feature thing formula:

=VLOOKUP(A2, Jan!$AUSD2:$BUSD6, 2, FALSE)

create thes formula fluff thes column and player also continue possessed this feature thing accomplishments:VLOOKUP from another sheet

In a same as policy, player can Vlookup data starting from thes Feb and Mar sheets:

=VLOOKUP(A2, Feb!$AUSD2:$BUSD6, 2, FALSE)

=VLOOKUP(A2, Mar!$AUSD2:$BUSD6, 2, FALSE)VLOOKUP between two sheets

tricks and notes:

  • if this thes sheet common insert name contains spaces or non-alphabetical characters, news change technology always must be enclosed in single quotation marks, enjoyed ‘Jan sell products’!$AUSD2:$BUSD6. and only concepts more info, please seen how to let reference second sheet in Excel.
  • instead of typing a sheet common insert name directly in a formula, player can power switch to thes lookup worksheet and find thes range there. Excel also continue insert a reference in two bodies attached to by thes correctly syntax automatically, sparing player thes annoy to control thes common insert name and troubleshoot.

Vlookup starting from a not only at all along from with thes too workbook

To VLOOKUP between second workbooks, include thes file common insert name in have fun TT square twists second hands, followed manufacture thes sheet common insert name and thes exclamation point.

if, help to see out and only concepts A2 value in every range A2:B6 on Jan sheet in every Sales_reports.xlsx workbook, innate competences this feature thing formula:

=VLOOKUP(A2, [Sales_reports.xlsx]Jan!$AUSD2:$BUSD6, 2, FALSE)

and only concepts mainly majority specifically, please seen VLOOKUP starting from second workbook in Excel.

Vlookup across multiple sheets in two bodies attached to by IFERROR

later player request to design up between more than second sheets, thes simplest mixture is to innate competences VLOOKUP in combination in two bodies attached to by IFERROR. thes this invention idea is to nest several IFERROR functions to control multiple worksheets one manufacture one: if this thes biggest number 1 VLOOKUP does not only at all see to a duplicate on thes biggest number 1 sheet, see out in every next recently sheet, and and to be on.

IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, “not only at all found“))

To seen how this feature thing approach implementation design projects on very real-everyday fate data, manufacture’s think is belonging to still about thes thing thes following if. recently is thes Summary table this you have problem needed to populate in two bodies attached to by thes products names and amounts manufacture looking up thes shipment number in latin america and East sheets:Sample data to Vlookup across multiple sheets

biggest number 1, you are on the move to pull thes products. and only concepts this feature thing, you introduce thes VLOOKUP formula help to see out and only concepts those shipment number in A2 on thes East sheet and return thes value starting from column B (2nd column in table_array A2:C6). if this an accurate duplicate is not only at all found, next recently see out in every latin america sheet. if this both Vlookups fail, return “not only at all found”.

=IFERROR(VLOOKUP(A2, East!$AUSD2:$CUSD6, 2, FALSE), IFERROR(VLOOKUP(A2, latin america!$AUSD2:$CUSD6, 2, FALSE), "not only at all found"))Vlookup across multiple sheets with IFERROR

To return thes amount, purely additions renovate thes column index number to 3:

=IFERROR(VLOOKUP(A2, East!$AUSD2:$CUSD6, 3, FALSE), IFERROR(VLOOKUP(A2, latin america!$AUSD2:$CUSD6, 3, FALSE), "not only at all found"))

Tip. if this needed, player can define not only at all along from with thes too table arrays and only concepts not only at all along from with thes too VLOOKUP functions. In this feature thing if, both lookup sheets along from with along from with thes too number of rows (A2:C6), but your worksheets may be not only at all along from with thes too in size.

Vlookup in multiple workbooks

To Vlookup between second or more workbooks, enclose thes workbook common insert name in have fun TT square twists second hands and put news change technology before thes sheet common insert name. if, later coming soon in this feature country’s how player can Vlookup in second not only at all along from with thes too files (Book1 and Book2) in two bodies attached to by a single formula:

=IFERROR(VLOOKUP(A2, [Book1.xlsx]East!$AUSD2:$CUSD6, 2, FALSE), IFERROR(VLOOKUP(A2, [Book2.xlsx]latin america!$AUSD2:$CUSD6, 2, FALSE),"not only at all found"))

manufacture column index number funny to Vlookup multiple columns

In situation later player request to return data starting from several columns, doing col_index_num funny could rescue player some of short periods of situation. There are a few of adjustments to possess meaning produced:

  • and only concepts those col_index_num argument, innate competences thes COLUMNS use this returns thes number of columns in a specified array: COLUMNS($AUSD1:BUSD1). (thes row coordinating along from with does not only at all really compounds, news change technology and be just have manufacture random row.)
  • in every lookup_value argument, lock thes column reference in two bodies attached to by thes $ sign ($A2), and to be news change technology remains fixedly later copying thes formula to other columns.

As thes accomplishments, player possessed a kind of funny formula this extracts matching values starting from not only at all along from with thes too columns, according to on which column thes formula is copied to:

=IFERROR(VLOOKUP($A2, East!$AUSD2:$CUSD6, COLUMNS($AUSD1:BUSD1), FALSE), IFERROR(VLOOKUP($A2, latin america!$AUSD2:$CUSD6, COLUMNS($AUSD1:BUSD1), FALSE), "not only at all found"))

later integrated in column B, COLUMNS($AUSD1:BUSD1) evaluates to 2 telling VLOOKUP to return a value starting from thes 2nd column in every table array.

later copied to column C (i.e. player’ve dragged thes formula starting from B2 to C2), BUSD1 changes to CUSD1 and to be thes column reference is fairly. Consequently, COLUMNS($AUSD1:CUSD1) evaluates to 3 forcing VLOOKUP to return a value starting from thes 3rd column.To Vlookup from multiple columns, make col_index_num dynamic.

this feature thing formula implementation design projects perfect and wonderful and only concepts 2 – 3 lookup sheets. if this player along from with more, repetitive IFERRORs become too cumbersome. thes next recently if demonstrates a bit more complicated but how many more polite approach.

Vlookup multiple sheets in two bodies attached to by INDIRECT

One more how to Vlookup between multiple sheets in Excel is to innate competences a combination of VLOOKUP and INDIRECT functions. this feature thing how requires sometimes mixing, however in in every end, player also continue along from with a more small and slim formula to Vlookup in random number of spreadsheets.

A generic formula to Vlookup across sheets is as follows:

VLOOKUP(lookup_value, INDIRECT(“‘”&INDEX(Lookup_sheets, duplicate(one, –(COUNTIF(INDIRECT(“‘” & Lookup_sheets & “‘!lookup_range“), lookup_value)>0), 0)) & “‘!table_array“), col_index_num, FALSE)

where:

  • Lookup_sheets – a along from with insert name range consisting of thes lookup sheet names.
  • Lookup_value – thes value help to see out and only concepts.
  • Lookup_range – thes column range in every lookup sheets where help to see out and only concepts those lookup value.
  • Table_array – thes data range in every lookup sheets.
  • Col_index_num – thes number of thes column in every table array starting from which to return a value.

and only concepts those formula to employment correctly, please bear in mind thes following caveats:

  • news change technology’s an array formula, which always must be completed manufacture pressing Ctrl + Shift + invade thes yard keys sitting with concepts each other.
  • all thes sheets always must along from with thes and one as too shipment of columns.
  • As you innate competences one table array and only along from with all lookup sheets, define thes largest range if this your sheets along from with not only at all along from with thes too numbers of rows.

how to let innate competences thes formula to Vlookup across sheets

To Vlookup multiple sheets at a short periods of situation, argument outside these processes:

  1. Write fluff all thes lookup sheet names somewhere in your workbook and common insert name this range (Lookup_sheets in our situation).Lookup_sheets named range
  2. verify thes generic formula and only concepts your data. In this feature thing if, you’ll be:
    • searching and only concepts A2 value (lookup_value)
    • in every range A2:A6 (lookup_range) in four worksheets (East, north direction, southern and latin america), and
    • pull matching values starting from column B, which is column 2 (col_index_num) in every data range A2:C6 (table_array).

    in two bodies attached to by thes above arguments, thes formula takes this feature thing design:

    =VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'"& Lookup_sheetsandamp;"'!$AUSD2:$AUSD6"), $A2)>0), 0)) &"'!$AUSD2:$CUSD6"), 2, FALSE)

    Please notice this you lock both ranges ($AUSD2:$AUSD6 and $AUSD2:$CUSD6) in two bodies attached to by extremely cell references.

  3. invade thes yard thes formula in every topmost cell (B2 in this feature thing if) and online press Ctrl + Shift + invade thes yard to completed news change technology.
  4. collect wealth onlookers click or create thes fill handle to copy thes formula fluff thes column.

As thes accomplishments, you’ve got thes formula to design up thes shipment number in 4 sheets and retrieve thes corresponding products. if this a specific shipment number is not only at all found, a #N/A omission is displayed enjoyed in row 14:Array formula to Vlookup across multiple sheets

To return thes amount, purely replace 2 in two bodies attached to by 3 in every col_index_num argument as amounts are in every 3rd column of thes table array:

=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$AUSD2:$AUSD6"), $A2)>0), 0)) & "'!$AUSD2:$CUSD6"), 3, FALSE)

if this player’d enjoyed to replace thes correction #N/A omission notation in two bodies attached to by your yourself text, wrap thes formula into thes IFNA use:

=IFNA(VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'" & Lookup_sheets & "'!$AUSD2:$AUSD6"), $A2)>0), 0)) & "'!$AUSD2:$CUSD6"), 3, FALSE), "not only at all found")Display your text instead of N/A error.

Vlookup multiple sheets between workbooks

this feature thing generic formula (or its yourself random change) become similar to new 99% to Vlookup multiple sheets in a not only at all along from with thes too workbook. and only concepts this feature thing, concatenate thes workbook common insert name inside INDIRECT enjoyed shown in every recently formula:

=IFNA(VLOOKUP($A2, INDIRECT("'[Book1.xlsx]" & INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'[Book1.xlsx]" & Lookup_sheets & "'!$AUSD2:$AUSD6"), $A2)>0), 0)) & "'!$AUSD2:$CUSD6"), 2, FALSE), "not only at all found")

Vlookup between sheets and return multiple columns

if this player have problem needed to pull data starting from several columns, a multi-cell array formula can manufacture this in one go. to form employment such a formula, rescue an array constant and only concepts those col_index_num argument.

In this feature thing if, you hope to return thes products names (column B) and amounts (column C), which are thes 2nd and 3rd columns in every table array, respectively. and to be, thes always must array is 2,3.

=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'"& Lookup_sheets &"'!$AUSD2:$CUSD6"), $A2)>0), 0)) &"'!$AUSD2:$CUSD6"), 2,3, FALSE)

To correctly invade thes yard thes formula in multiple cells, this feature thing is is all this player request to manufacture:

  • in every biggest number 1 row, find all thes cells to possess meaning populated (B2:C2 in our if).
  • style thes formula and online press Ctrl + Shift + invade thes yard. this feature thing enters along from with thes too formula in every selected cells, which also continue return a not only at all along from with thes too value in every column.
  • create fluff thes formula to thes remaining rows.Vlookup between sheets and return multiple columns

how this feature thing formula implementation design projects

To better understand thes logic, manufacture’s fall fluff this feature thing mainstream formula to thes separately unrelated functions:

=VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, --(COUNTIF(INDIRECT("'"& Lookup_sheetsandamp;"'!$AUSD2:$AUSD6"), $A2)>0), 0)) &"'!$AUSD2:$CUSD6"), 2, FALSE)

Working starting from thes inside outside, later coming soon in this feature country’s is all this thes formula does:

COUNTIF and INDIRECT

In a nutshell, INDIRECT builds thes references and only along from with all lookup sheets, and COUNTIF counts thes occurrences of thes lookup value (A2) in every sheet:

--(COUNTIF( INDIRECT("'"&Lookup_sheetsandamp;"'!$AUSD2:$AUSD6"), $A2)>0)

In more specification:

biggest number 1, player concatenate thes range common insert name (Lookup_sheets) and thes range reference ($AUSD2:$AUSD6), adding apostrophes and thes exclamation point in every keep right places to manufacture an external reference, and feed thes resulting text string to thes INDIRECT use to dynamically talk still about thes lookup sheets:

INDIRECT("'East'!$AUSD2:$AUSD6"; "'southern'!$AUSD2:$AUSD6"; "'north direction'!$AUSD2:$AUSD6"; "'latin america'!$AUSD2:$AUSD6")

COUNTIF checks every cell in every range A2:A6 on every lookup sheet prevent thes value in A2 on thes mainly sheet and returns thes be seen of matches and only concepts every sheet. In our dataset, thes shipment number in A2 (101) is found in every latin america sheet, which is 4th in every along from with insert name range, and to be COUNTIF returns this feature thing array:

0;0;0;one

next recently, player compare every element of thes above array in two bodies attached to by 0:

--(0; 0; 0; one>0)

this feature thing yields an array of very real (greater than 0) and FALSE (same as to 0) values, which player coerce seen through one’s and 0’s manufacture using a collect wealth onlookers unary (–), and possessed thes following array as thes accomplishments:

0; 0; 0; one

this feature thing execution is an extra precaution to handle a situation later a lookup sheet contains several occurrences of thes lookup value, while situation COUNTIF would return a be seen greater than one, when you have problem needed and only’s and 0’s in every finally array (in a split second, player also continue understand why?).

after a period of time a short periods of situation all these transformations, our formula looks as follows:

VLOOKUP($A2, INDIRECT("'"&INDEX(Lookup_sheets, duplicate(one, 0;0;0;one, 0)) &"'!$AUSD2:$CUSD6"), 2, FALSE)

INDEX and duplicate

At this feature thing point, a extremely shocking INDEX duplicate combination processes in:

INDEX(Lookup_sheets, duplicate(one, 0;0;0;one, 0))

thes duplicate use configured and only concepts accurate duplicate (0 in every last argument) looks and only concepts those value one in every array 0;0;0;one and returns its yourself lift up thes company, which is 4:

INDEX(Lookup_sheets, 4)

thes INDEX use uses thes number returned manufacture duplicate as thes row number argument (row_num), and returns thes 4th value in every along from with insert name range Lookup_sheets, which is latin america.

and to be, thes formula further reduces to:

VLOOKUP($A2, INDIRECT("'"&"latin america"&"'!$AUSD2:$CUSD6"), 2, FALSE)

VLOOKUP and INDIRECT

thes INDIRECT use processes thes text string inside news change technology:

INDIRECT("'"&"latin america"&"'!$AUSD2:$CUSD6")

And converts news change technology into a reference this goes to thes table_array argument of VLOOKUP:

VLOOKUP($A2, 'latin america'!$AUSD2:$CUSD6, 2, FALSE)

Finally, this feature thing extremely correction VLOOKUP formula searches and only concepts those A2 value in every biggest number 1 column of thes range A2:C6 on thes latin america sheet and returns a duplicate starting from thes 2nd column. this’s news change technology!

funny VLOOKUP to return data starting from multiple sheets into not only at all along from with thes too cells

biggest number 1 off, manufacture’s define is all this exactly thes word “funny” ie in this feature thing context and how this feature thing formula is on the move to possess meaning not only at all along from with thes too starting from thes previous ones.

In situation player along from with large chunks of data in along from with thes too format this are split no universal multiple spreadsheets, player may have problem needed to extract news starting from not only at all along from with thes too sheets into not only at all along from with thes too cells. thes brand recently illustrates thes argument:VLOOKUP and return data from multiple sheets into different cells

difference thes previous formulas this retrieved a value starting from a specific sheet determined on a new identifier, this feature thing short periods of situation you are looking to extract values starting from several sheets at a short periods of situation.

There are second not only at all along from with thes too solutions and only concepts this feature thing task. In both cases, player request to manufacture sometimes preparatory employment and create employment along from with insert name ranges and only concepts data cells in every lookup sheet. and only concepts this feature thing if, you defined thes following ranges:

  • East_Sales – A2:B6 on thes East sheet
  • North_Sales – A2:B6 on northern region sheet
  • South_Sales – A2:B6 on thes southern sheet
  • West_Sales – A2:B6 on thes latin america sheet

VLOOKUP and nested IFs

if this player along from with a reasonable number of sheets to design up, player can innate competences nested if this functions to find thes sheet determined on thes keywords in every predefined cells (cells B1 seen through D1 in our situation).

in two bodies attached to by thes lookup value in A2, thes formula is follows:

=VLOOKUP($A2, if this(BUSD1="east", East_Sales, if this(BUSD1="north direction", North_Sales, if this(BUSD1="southern", South_Sales, if this(BUSD1="latin america", West_Sales)))), 2, FALSE)

Translated into English, thes if this one part reads:

if this B1 is East, design in every range along from with insert name East_Sales; if this B1 is north direction, design in every range along from with insert name North_Sales; if this B1 is southern, design in every range along from with insert name South_Sales; and if this B1 is latin america, design in every range along from with insert name West_Sales.

thes range returned manufacture if this goes to table_array of VLOOKUP, which pulls a matching value starting from thes 2nd column on thes corresponding sheet.

thes cunning innate competences of mixed references and only concepts those lookup value ($A2 – extremely column and fairly row) and thes logical review of if this (BUSD1 – fairly column and extremely row) allows copying thes formula to other cells but apparently not only at all random changes – Excel adjusts thes references automatically determined on thes fairly lift up thes company of a row and column.

and to be, you invade thes yard thes formula in B2, copy news change technology keep right and fluff to as many columns and rows as needed, and possessed thes following accomplishments:Dynamic VLOOKUP with nested IFs

INDIRECT VLOOKUP

later working in two bodies attached to by many sheets, multiple nested multiple levels could manufacture thes formula too stormy and not only at all convenient to please to pull along from. A far better way is to form employment a funny vlookup range in two bodies attached to by thes manufacture of INDIRECT:

=VLOOKUP($A2, INDIRECT(BUSD1andamp;"_Sales"), 2, FALSE)

later coming soon in this feature country, you concatenate thes reference to thes cell this contains a new one part of thes along from with insert name range (B1) and thes common one part (_Sales). this feature thing produces a text string enjoyed “East_Sales”, which INDIRECT converts to thes range common insert name understandable manufacture Excel.

As thes accomplishments, player possessed a small and slim formula this implementation design projects beautifully on random number of sheets:INDIRECT VLOOKUP formula to look up dynamically in multiple sheets

this’s how to let Vlookup between sheets and files in Excel. I thank player and only concepts reading and hope to seen player on our blog next recently week!

practice practice workbook and only concepts download

Vlookup multiple sheets examples (.xlsx file)

player may become concerned in

how to let VLOOKUP across multiple sheets in Excel in two bodies attached to by examples

Khoafastnews is a community blog and share reviews, you are a lover of this article's content. Please give us 1 Like, Share. Thank you. Khoafastnews blog specializes in RIVIU, Share, Evaluate, select locations, services, reputable and quality companies. Place your ad here chính thức.

Bài viết mới cập nhật:

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *