Advanced VLOOKUP in Excel: multiple, double, nested

These examples will teach you how to Vlookup multiple criteria, return a specific instance or all matches, do dynamic Vlookup in multiple sheets, and more.

It is the second part of the series that will help you harness the power of Excel VLOOKUP. The examples imply that you know how this function works. If not, it stands to reason to start with the basic uses of VLOOKUP in Excel.

Before moving further, let me briefly remind you the syntax:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Now that everyone is on the same page, let's take a closer look at the advanced VLOOKUP formula examples:

How to Vlookup multiple criteria

The Excel VLOOKUP function is really helpful when it comes to searching across a database for a certain value. However, it lacks an important feature - its syntax allows for just one lookup value. But what if you want to look up with several conditions? There are a few different solutions for you to choose from.

Formula 1. VLOOKUP with two criteria

Suppose you have a list of orders and want to find the quantity based on 2 criteria, Customer name and Product. A complicating factor is that each customer ordered multiple products, as shown in the table below:
VLOOKUP based on two values – source data

A usual VLOOKUP formula won't work in this situation because it returns the first found match based on a single lookup value that you specify.

To overcome this, you can add a helper column and concatenate the values from two lookup columns (Customer and Product) there. It is important that the helper column should be the leftmost column in the table array because it's where Excel VLOOKUP always searches for the lookup value.

So, add a column to the left of your table and copy the below formula across that column. This will populate the helper column with the values from columns B and C (the space character is concatenated in between for better readability):

=B2&" "&C2

And then, use a standard VLOOKUP formula and place both criteria in the lookup_value argument, separated with a space:

=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)

Or, input the criteria in separate cells (G1 and G2 in our case) and concatenate those cells:

=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)

As we want to return a value from column D, which is fourth in the table array, we use 4 for col_index_num. The range_lookup argument is set to FALSE to Vlookup an exact match. The screenshot below shows the result:
VLOOKUP with two criteria

In case your lookup table is in another sheet, include the sheet's name in your VLOOKUP formula. For example:

=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)

Alternatively, create a named range for the lookup table (say, Orders) to make the formula easier-to-read:

=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)

For more information, please see How to Vlookup from another sheet in Excel.

Note. For the formula to work correctly, the values in the helper column should be concatenated exactly the same way as in the lookup_value argument. For example, we used a space character to separate the criteria in both the helper column (B2&" "&C2) and VLOOKUP formula (G1&" "&G2).

Formula 2. Excel VLOOKUP with multiple conditions

In theory, you can use the above approach to Vlookup more than two criteria. However, there are a couple of caveats. Firstly, a lookup value is limited to 255 characters, and secondly, the worksheet's design may not allow adding a helper column.

Luckily, Microsoft Excel often provides more than one way to do the same thing. To Vlookup multiple criteria, you can use either an INDEX MATCH combination or the XLOOKUP function recently introduced in Office 365.

For example, to look up based on 3 different values (Date, Customer name and Product), use one of the following formulas:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

Where:

  • G1 is criteria 1 (date)
  • G2 is criteria 2 (customer name)
  • G3 is criteria 3 (product)
  • A2:A11 is lookup range 1 (dates)
  • B2:B11 is lookup range 2 (customer names)
  • C2:C11 is lookup range 3 (products)
  • D2:D11 is the return range (quantity)

VLOOKUP multiple criteria

Note. In all versions except Excel 365, INDEX MATCH should be entered as an CSE array formula by pressing Ctrl + Shift + Enter. In Excel 365 that supports dynamic arrays it also works as a regular formula.

For the detailed explanation of the formulas, please see:

How to use VLOOKUP to get 2nd, 3rd or nth match

As you already know, Excel VLOOKUP can fetch only one matching value, more precisely, it returns the first found match. But what if there are several matches in your lookup array and you want to get the 2nd or 3rd instance? The task sounds quite intricate, but the solution does exist!

Formula 1. Vlookup Nth instance

Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2nd or 3rd product bought by a given customer.

The simplest way is to add a helper column to the left of the table like we did in the first example. But this time, we will populate it with customer names and occurrence numbers like "John Doe1", "John Doe2", etc.

To get the occurrence, use the COUNTIF function with a mixed range reference (the first reference is absolute and the second is relative like $B$2:B2). Since the relative reference changes based on a position of the cell where the formula is copied, in row 3 it will become $B$2:B3, in row 4 - $B$2:B4, and so on.

Concatenated with the customer name (B2), the formula takes this form:

=B2&COUNTIF($B$2:B2, B2)

The above formula goes to A2, and then you copy it down to as many cells as needed.

After that, input the target name and occurrence number in separate cells (F1 and F2), and use the below formula to Vlookup a specific occurrence:

=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Vlookup Nth instance

Formula 2. Vlookup 2nd occurrence

If you are looking for the 2nd instance of the lookup value, then you can do without the helper column. Instead, create the table array dynamically by using the INDIRECT function together with MATCH:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)

Where:

  • E1 is the lookup value
  • A2:A11 is the lookup range
  • B11 is the last (bottom-right) cell of the lookup table
    Vlookup 2nd occurrence

Please note that the above formula is written for a specific case where data cells in the lookup table begin in row 2. If your table is somewhere in the middle of the sheet, use this universal formula, where A1 is the top-left cell of the lookup table containing a column header:

=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)

How this formula works

Here is the key part of the formula that creates a dynamic vlookup range:

INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")

The MATCH function configured for exact match (0 in the last argument) compares the target name (E1) against the list of names (A2:A11) and returns the position of the first found match, which is 3 in our case. This number is going to be used as the starting row coordinate for the vlookup range, so we add 2 to it (+1 to exclude the first instance and +1 to exclude row 1 with the column headers). Alternatively, you can use 1+ROW(A1) to calculate the necessary adjustment automatically based on the position of the header row (A1 in our case).

As the result, we get the following text string, which INDIRECT converts to a range reference:

INDIRECT("A"&5&":B11") -> A5:B11

This range goes to the table_array argument of VLOOKUP forcing it to start searching in row 5, leaving out the first instance of the lookup value:

VLOOKUP(E1, A5:B11, 2, FALSE)

How to Vlookup and return multiple values in Excel

The Excel VLOOKUP function is designed to return just one match. Is there a way to Vlookup multiple instances? Yes, there is, though not an easy one. This requires a combined use of several functions such as INDEX, SMALL and ROW is an array formula.

For example, the below can find all occurrences of the lookup value F2 in the lookup range B2:B16 and return multiple matches from column C:

{=IFERROR(INDEX($C$2:$C$11, SMALL(IF($F$1=$B$2:$B$11, ROW($C$2:$C$11)-1,""), ROW()-1)),"")}

There are 2 ways to enter the formula in your worksheet:

  1. Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
  2. Select several adjacent cells in a single column (F1:F11 in the screenshot below), type the formula and press Ctrl + Shift + Enter to complete it.

Either way, the number of cells in which you enter the formula should be equal to or larger than the maximum number of possible matches.
Vlookup multiple values

For the detailed explanation of the formula logic and more examples, please see How to VLOOKUP multiple values in Excel.

How to Vlookup in rows and columns (two-way lookup)

Two-way lookup (aka matrix lookup or 2-dimentional lookup) is a fancy word for looking up a value at the intersection of a certain row and column. There are a few different ways to do two-dimensional lookup in Excel, but since the focus of this tutorial is on the VLOOKUP function, we will naturally use it.

For this example, we'll take the below table with monthly sales and work out a VLOOKUP formula to retrieve the sales figure for a specific item in a given month.

With item names in A2:A9, month names in B1:F1, the target item in I1 and the target month in I2, the formula goes as follows:

=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Vlookup in rows and columns

How this formula works

The core of the formula is the standard VLOOKUP function that searches for an exact match to the lookup value in I1. But since we do not know in which exactly column the sales for a specific month are, we cannot supply the column number directly to the col_index_num argument. To find that column, we use the following MATCH function:

MATCH(I2, A1:F1, 0)

Translated into English, the formula says: look up the I2 value in A1:F1 and return its relative position in the array. By supplying 0 to the 3rd argument, you instruct MATCH to find the value exactly equal to the lookup value (it's like using FALSE for the range_lookup argument of VLOOKUP).

Since Mar is in the 4th column in the lookup array, the MATCH function returns 4, which goes directly to the col_index_num argument of VLOOKUP:

VLOOKUP(I1, A2:F9, 4, FALSE)

Please pay attention that although the month names start in column B, we use A1:I1 for the lookup array. This is done in order for the number returned by MATCH to correspond to the column's position in table_array of VLOOKUP.

To learn more ways to perform matrix lookup in Excel, please see INDEX MATCH MATCH and other formulas for 2-dimensional lookup.

How to do multiple Vlookup in Excel (nested Vlookup)

Sometimes it may happen that your main table and lookup table do not have a single column in common, which prevents you from doing a Vlookup between two tables. However, there exists another table, which does not contain the information you are looking for but has one common column with the main table and another common column with the lookup table.

In below image illustrates the situation:
Nested Vlookup in Excel

The goal is to copy prices to the main table based on Item IDs. The problem is that the table containing prices does not have the Item IDs, meaning we will have to do two Vlookups in one formula.

For the sake of convenience, let's create a couple of named ranges first:

  • Lookup table 1 is named Products (D3:E10)
  • Lookup table 2 is named Prices (G3:H10)

The tables can be in the same or different worksheets.

And now, we will perform the so-called double Vlookup, aka nested Vlookup.

First, make a VLOOKUP formula to find the product name in the Lookup table 1 (named Products) based on the item id (A3):

=VLOOKUP(A3, Products, 2, FALSE)

Next, put the above formula in the lookup_value argument of another VLOOKUP function to pull prices from Lookup table 2 (named Prices) based on the product name returned by the nested VLOOKUP:

=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)

The screenshot below shows our nested Vlookup formula in action:
Multiple (nested) Vlookup in Excel

How to Vlookup multiple sheets dynamically

Sometimes, you may have data in the same format split over several worksheets. And your aim is to pull data from a specific sheet depending on the key value in a given cell.

This may be easier to understand from an example. Let's say, you have a few regional sales reports in the same format, and you are looking to get the sales figures for a specific product in certain regions:
VLOOKUP multiple sheets dynamically

Like in the previous example, we start with defining a few names:

  • Range A2:B5 in CA sheet is named CA_Sales.
  • Range A2:B5 in FL sheet is named FL_Sales.
  • Range A2:B5 in KS sheet is named KS_Sales.

As you can see, all the named ranges have a common part (Sales) and unique parts (CA, FL, KS). Please be sure to name your ranges in a similar manner as it's essential for the formula we are going to build.

Formula 1. INDIRECT VLOOKUP to dynamically pull data from different sheets

If your task is to retrieve data from multiple sheets, a VLOOKUP INDIRECT formula is the best solution – compact and easy-to-understand.

For this example, we organize the summary table in this way:

  • Input the products of interest in A2 and A3. Those are our lookup values.
  • Enter the unique parts of the named ranges in B1, C1 and D1.

And now, we concatenate the cell containing the unique part (B1) with the common part ("_Sales"), and feed the resulting string to INDIRECT:

INDIRECT(B$1&"_Sales")

The INDIRECT function transforms the string into a name that Excel can understand, and you put it in the table_array argument of VLOOKUP:

=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)

The above formula goes to B2, and then you copy it down and to the right.

Please pay attention that, in the lookup value ($A2), we've locked the column coordinate with absolute cell reference so that the column remains fixed when the formula is copied to the right. In the B$1 reference, we locked the row because we want the column coordinate to change and supply an appropriate name part to INDIRECT depending on the column into which the formula is copied:
VLOOKUP and INDIRECT to dynamically pull data from multiple sheets

If your main table is organized differently, the lookup values in a row and unique parts of the range names in a column, then you should lock the row coordinate in the lookup value (B$1) and the column coordinate in the name parts ($A2):

=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
INDIRECT VLOOKUP in Excel

Formula 2. VLOOKUP and nested IFs to look up multiple sheets

In situation when you have just two or three lookup sheets, you can use a fairly simple VLOOKUP formula with nested IF functions to select the correct sheet based on the key value in a particular cell:

=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)

Where $A2 is the lookup value (item name) and B$1 is the key value (state):
VLOOKUP and nested IFs to return data from multiple sheets

In this case, you do not necessarily need to define names and can use external references to refer to another sheet or workbook.

For more formula examples, please see How to VLOOKUP across multiple sheets in Excel.

That's how to use VLOOKUP in Excel. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Advanced VLOOKUP formula examples (.xlsx file)

540 comments

  1. i need a formula for the worksheet. can i have your email address. i will attach the excel sheet. please!

  2. greetings, i have a following query,

    Dealers july aug sep
    parts oil parts oil parts oil
    A 100 50 80 30 70 40
    B 120 45 115 50 125 55
    C

    i have a combined data of 3 dealers month wise with sub categories. i want to have a separate sheet which shows me target of a specific month. like if i ask July it shall show july targets of all dealers with sub categories.

  3. How can I vlookup for each agent daily on another sheet to show lateness,absence and presence? Using data from response form submitted by each offline agents which appears in the format below.

    Many thanks!

    A1 = Check in time per date (9/12/2017 16:55:00, 9/13/2017 16:55:00....)
    B C D E F G
    Clock in (Names) MOD(A2,1) MOD(B2,1) Exceeds (Late15mins)
    9/12/201716:58:01 Olalekan 4:55:00 PM 4:58:01 PM 00:03 Early
    9/12/2017 16:58:08 Ikechukwu 4:55:00 PM 4:58:08 PM 00:03 Early
    9/12/2017 16:58:29 Damilare 4:55:00 PM 4:58:29 PM 00:03 Early
    9/12/2017 16:58:33 Abieu M 4:55:00 PM 4:58:33 PM 00:03 Early
    9/12/2017 16:59:02 Ruth N 4:55:00 PM 4:59:02 PM 00:04 Early
    9/12/2017 16:59:27 Anosike 4:55:00 PM 4:59:27 PM 00:04 Early

  4. Hi
    Is it possible to do a lookup for a narrative when the narrative differs slightly from tab to tab - so not an exact match.
    Eg the narrative I want to look up is "Jimmy Choo 40ml EDT" but on another spreadsheet it is "Jimmy Choo 40ml EDT Spray".
    Any help would be appreciated.
    thanks
    kandie

  5. I need to lookup value of Product with No having latest date.

    Product No Date
    A 750000 14-09-2017
    A 85101 15-09-2017
    A 14413107 16-09-2017
    B 41351 14-09-2017
    B 1345654 15-09-2017
    B 1531546 16-09-2017

    • Hello,

      if you find the formula in the article above a bit complicated or you'd like to get a quicker and simpler solution, please take a look at our Vlookup Wizard add-in. You will find it in Ultimate Suite collection that can be downloaded from this web-page. The add-in can be used instead of VLOOKUP function and will return the value you need in a couple of clicks.

      Hope it helps!

  6. Hello

    I have one problem that I can't solve it, I want to use multiple row in Vlookup, but i can't do it.

    Example: IFERROR(VLOOKUP(A2:Z2,Data-sheet,2,0), "") the result can't, but if i use only one row IFERROR(VLOOKUP(A2,Data-sheet,2,0), "") it automatically show the result.

    Please advice me because i need to do with multiple row.

    Best,

  7. How to apply the Vlookup or any other formula on long written statements.
    These statements normally written in one cell.
    Like a
    On-line Banking bill payment to DHL Express Ref:-417930361
    On-line Banking bill payment to TCS Express Ref:-417930361
    I want to apply the Vlookup or any other formula on whole statement
    Fore example if in side the statement there is word DHL, then its should write 1 otherwise, zero.
    Please help for this issue or selecting the formula.
    Thank You
    Zubair

  8. Hi, I have created a training report to pull the completion status of each of the trainee in my list. We do have several course translations so the challenge is that, I am not sure which among the course language translations did the trainee take to be marked "completed". I am taking the information from a learning management system's raw data but it contains 77k rows from multiple countries. I tried using a combination of nested IFERRORa and VLOOKUP functions to do this, and is using a helper column to combine the course name and the trainee's User ID then I added this in the first column of my massive raw data while the status comes next to it.

    SAMPLE COURSE TRANSLATIONS
    Cell X4: Course1 (English)
    Cell X5: Course1 (Chinese)
    Cell X6: Course1 (Simplified Chinese)
    Cell X7: Course1 (ZH Chinese)

    SAMPLE UNIQUE USERID
    Cell A2: 123456

    LookUP Table: WBT

    My code looks like this;

    =IFERROR(VLOOKUP(A2&" "&X4,WBT,2,FALSE),IFERROR(VLOOKUP(A2&" "&X5,WBT,2,FALSE),IFERROR(A2&" "&X6,WBT,2,FALSE),IFERROR(A2&" "&X7,WBT,2,FALSE),""))))

    This formula does not return a value when for example the completed course is X5, X6, or X7. If the completed course is X5 and I put X5 in my first vlookup's lookup value, it returns the correct status. That means, it only runs the first vlookup formula.

    One observation when I try running this formula below and when the completed course is X5,it doesn't return a #NA but just a blank cell. The 3rd, and 4th Vlookup when ran independently and the completed course is X5, returns #NA

    =(VLOOKUP(A2&" "&X4,WBT,2,FALSE)

    Any idea? i hope i was able to explain it correctly though. Thank you in advanced

  9. Dear Svetlana,
    May I know some clarity on this as mu trail was not successful.

    {=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}

    Copy the below formula to several adjacent cells, e.g. cells F4:F8 as demonstrated in the screenshot below. The number of cells where you copy the formula should be equal to or larger than the maximum number of possible duplicate entries. Also, remember to press Ctrl + Shift + Enter to enter an array formula correctly.
    Getting all duplicate occurrences of the lookup value

    Thanks much in advance.

    Best
    Shofikul

  10. I have a qusition.
    is there anyway to do vlook from a table to bring the value from a cells in different column and raw in the same time.
    for example.

    products factor Jan Feb March
    A fcst 5 7 8
    A actual 4 5 9

    B fcst 10 9 15
    B actual 12 8 14

    I need to make a table to read only i.e. fcst for A products in the specific month.

    is there anyway to do it specially when you have huge database.

  11. I am trying to get a formula to work. I have a call log in which I would like to see if a voicemail that was left, was returned after the original voicemail was received. I came up with: =IF(A2="Sent to Voicemail",IF(VLOOKUP(B2,C3:C$7,1,FALSE),"Voicemail Returned","Voicemail not Returned"),"")

    Column A = Disposition
    Column B = ANI
    Column C = DNIS
    Column E = Voicemail Return (Formula Row)

    DISPOSITION ANI DNIS Voicemail Return
    Sent To Voicemail 4078675309 9876543210 Voicemail Returned
    Follow up Call Attempt 9876543210 4078675309
    Follow up Call Complete 5103359999 9876543210
    Sent To Voicemail 5102999999 9876543210 #N/A
    Caller Disconnected 9253009999 9876543210
    Follow up Call Attempt 9876549999 9158509999

    Any help would be greatly appreciated
    Warren

  12. Hi

    Thank you so much for the article. It is really very helpful.

    I am stuck at a point. I am trying to apply formula for duplicate entries. In your example, you have one customer name with multiple duplicate values. My sheet has got multiple customer names with duplicate values. I used the formula in the article, but the formula gives a nil value after 5 entries. Can you please help.?

    Thanks
    Prithi

  13. Hi I came across this function
    =Iferror(Proper(Concanate(VLook Up($A2,RIC,2,False),","(Vlook Up $A2,RIC,3FALSE))).

    I want to know where RIC comes from

  14. I wanna use the data in sheet 1 and get the simple output on the sheet 2 but the exact value in the corresponding label tab only. I have sent you the mail with the example workbook. looking for your reply here or on the email.

    Thanks!!

  15. maybe you can help me.

    I have a table where someone enters data based on a part number, date, and units pulled.

    I have a Vlookup formula on another sheet that totals the units pulled, based off part number.

    What I want to do is modify the Vlookup formula to allow us to total the same data, but for a particular date range.

    What i want to do is have an independent Cell on top at "A1" where we would enter the cut off date.

    And then in the Vlookup formula for the tables add an if formula to the criteria that will let tell it to only pull data if the date in the date column

    I know Vlookup is limited to One condition, and I may just be crazy, but this would be a huge help, otherwise I will be writing a bunch of If formulas pointing this all over the place.

    How can I make this work?

    Thank you.

  16. i am giving the below format i an unable to understand how can use the vlookup formula in in this condition

    I WANT THIS CUSTOMER CODE (1335) APR SALE AUTOMATIC IN APPEAR FY 15-16

    EX-

    SHEET 01

    CUSTOMER CODE CUSTOMER NAME
    1335 BALAJI SERVICE CENTRE
    FY MONTH FY 15-16
    Apr 0
    May 0
    Jun 0
    Jul 0
    Aug 0
    Sep 0
    Oct 0
    Nov 0
    Dec 0
    Jan 0
    Feb 0
    Mar 0

    SHEET02

    Fiscal Month Cons Party Code Cons Party Name Cons Party Type Desc Net Retail Selling
    Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER -687.2
    Apr TUA0203882 SIMRAN AUTO TRADER/RETAILER 3,256.4
    Apr TUA0205283 AJEET MOTORS TRADER/RETAILER 845.8
    Apr TUA0205284 ALMORA AUTO CARE TRADER/RETAILER 30,212.4
    Apr TUA0205285 AMOL AUTOMOBILES TRADER/RETAILER 24,195.8
    Apr TUA0205286 ARORA AUTO SPARES TRADER/RETAILER 58,845.3
    Apr TUA0205289 BAJRANG AUTOMOBILES TRADER/RETAILER 7,341.0
    Apr TUA0205290 BALBEER MOTORS TRADER/RETAILER 91,719.8
    Apr TUA0205292 BANSAL TRACTORS TRADER/RETAILER 38,561.1
    Apr TUA0205293 BATRA MOTORS TRADER/RETAILER 12,619.4

  17. Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

    for example

    for 2 if satnam is present then the value should appear in from of two only specific value "Satnam" Note

    2-Satnam 12345 2
    3-Kiv 4567
    4-New 9756

    • Hi if I wana do an vlookup with an condition stating some specific word if that reflect the beside it only then the value in the column should be captured can someone help me with the it...???

      for example

      for 2 serial number if satnam is present then the value should appear in front of two only specific value "Satnam"

      2-Satnam 12345
      3-Kiv 4567
      4-New 9756

      2

  18. i want to split qty of single cell.

    Input
    Sheet -1
    Material PO qty Req. Qty
    XX1 1 300
    XX1 2 200
    XX1 3 200
    XX1 4 350
    XX1 5 500
    XX1 6 200
    XX1 7 200
    XX1 8 200

    Material Batch Available qty
    XX1 a 100
    XX1 b 100
    XX1 c 100
    XX1 d 750
    XX1 e 250
    XX1 f 250
    XX1 g 600

    Req. Output as below

    PO qty Req. Qty Batch Available qty Material remarks
    1 100 a 100 XX1 PO qty Spilt
    1 100 b 100 XX1 PO qty Spilt
    1 100 c 100 XX1 PO qty Spilt
    2 200 d 200 XX1 Batch qty Spilt
    3 500 d 200 XX1 Batch qty Spilt
    4 200 d 350 XX1 Batch qty Spilt
    5 250 e 250 XX1 PO qty Spilt
    5 250 f 250 XX1 PO qty Spilt
    6 200 g 200 XX1 Batch qty Spilt
    7 200 g 200 XX1 Batch qty Spilt
    8 200 g 200 XX1 Batch qty Spilt

  19. Hi,

    I request guidance to solve below..

    Need to compare one cell value with below table (A to P) and map 5th column value..

    for eg Y = 33
    C=22
    P=44

    A B C D 22
    X Y Z K 33
    M N O P 44

    Lookup tables are working for either row wise or column wise,

    please support..

  20. Hi,

    I am having an issue with building a report. I am trying to identify Precinct information by a street number range. I am attempting to do so by using a Vlookup to find the street (working).With the numbers by if statements to say that 17 A St falls within the A St 1 to 20 range 1 being in cell b2 and 20 being in cell c2.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)