*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:

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.

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:

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:

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.

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)

For the detailed explanation of the formulas, please see:

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 2^{nd} or 3^{rd} instance? The task sounds quite intricate, but the solution does exist!

Suppose you have customer names in one column, the products they purchased in another, and you are looking to find the 2^{nd} or 3^{rd} 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)`

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

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)`

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)`

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$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}`

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

- Type the formula in the first cell, press Ctrl + Shift + Enter, and then drag it down to a few more cells.
- 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.

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

*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)`

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.

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:

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:E3). - Lookup table 2 is named
*Prices*(*G3:H3*).

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:

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:

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.

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:

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)`

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):

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!

Advanced VLOOKUP formula examples (.xlsx file)

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 329 responses to "How to use VLOOKUP in Excel: advanced formula examples"

hello,

a have a question based on your example please:

if a have the next situation:

Dan Brown A B

Dan Brown C D

Jeremy Hill T I

Dan Brown R T

and I want to have the next result into another sheet :

Dan Brown A B C D R T

Jeremy Hill T I

How can I do that?

Hello Octav,

Please see our Combine Rows Wizard add-in:

https://www.ablebits.com/excel-combine-rows/index.php

In my opinion it help for your task if the values in columns A and B are merged into one.

Below was the table my scope of search

Customer Name Product

Brown Apples

HILL CHock

Brown Sweets

Acey Lollypop

Wolf chikky

Brown Biscuits

Hill Alapino

Wolf Jelly

Hill gems

Search Cell in the same Sheet like the following

$G2 $H2

Dan Brown Apples

Sweets

Biscuits

$G$5

Hill ????

????

?????

wolf

I had used the following formula to get the first set of search:

=IFERROR(INDEX($C$2:$C$16,SMALL(IF($G$2=$B$2:$B$16,ROW($C$2:$C$16)-1,""),ROW()-1)),"")

I like some help to expand (or) new formula to list all the duplicate values with respect to the string mention within the Left side, instead of hardcording the right side formula....that is $G$2

Thanks for looking and trying to help me out...!!!

Hello Pratap,

I recommend sorting the list and adding a column with the following formula:

=IF(COUNTIF($H$5:H5,H5)=1,CONCATENATE(H5," ",I5),I5)

Feel free to download a sample file that shows how it works:

https://support.ablebits.com/blog_samples/vlookup-formula-examples_102.xlsx

Hey,

I loved you instructions! I have one question.

I am using INDEX MATCH to find a cell from another workbook and place the name of the cell near it. For example:

=INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH($B$10,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0))

Which works fine. But sometimes the "time" in the cell changes. For example $B$10 is "6:00 AM - 2:00 PM [Breads Sales]" but sometimes I use an employee that is only "6:00 AM - 1:30 PM [Breads Sales]". How can i make INDEX MATCH use 2 lookup values incase the first one fails?

Hello Argenis,

Try the IFERROR function that allows you to return another specified formula if the first one returns an error:

=IFERROR(INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$10_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)),

INDEX('[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$A$8:$A$75,MATCH(_$B$11_,'[Schedule 11.30.15 Hotschedules.xlsx]Sheet1'!$C$8:$C$75,0)))

https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611

Thank you, It worked wonderfully!!!

I have a large data for groups for e.g.

GRP_1 GRP_2 GRP_3

98465 5521 65466

65468 6663 6541

68465 6545 36541

65466 8466 6541

65466 9548 65666

and I want to create a list of products and which group it belongs like

Product Groups

5521 ??

6541 ??

6541 ?

6545

6663

8466

9548

36541

65466

65466

65466

65468

65666

68465

98465

Thanks

Hello, Ashwin,

Please try this formula:

=IF(ISERROR(MATCH(E2, $A$2:$A$6, 0)), IF(ISERROR(MATCH(E2, $B$2:$B$6, 0)), IF(ISERROR(MATCH(E2, $C$2:$C$6, 0)), "",$C$1), $B$1), $A$1)

I love this Page.... I am a self learner and got lot of help from this page....

I have 4 columns of Reg. No. Name Subject and Grade. I want to return the grade of specific subject of a student how can I use the vlookup formula.

Hello Mubarak,

Please see the first part of this tutorial which describes examples of problems similar to yours:

https://www.ablebits.com/office-addins-blog/2014/07/17/excel-vlookup-tutorial-beginner/

great site......

could you help me with the below query.....

I am doing vlookup, my ref column will have duplicate but I need all their corresponding items in one single cell line by line... can u help me on this....

Hello Sudhakar,

Thank you for your feedback.

I'm sorry, but we don't know of a simple way to get all values in one cell. You may need to use VBA for this task.

You can get all values in different cells, please see the "Get all duplicate occurrences of the lookup value" section for a detailed description.

Then you can use the Concatenate function to merge all the values you get into one cell. Please see this post for more information:

https://www.ablebits.com/office-addins-blog/2015/07/15/excel-concatenate-strings-cells-columns/

need your help on this. I want to vlook up for the specific number from another worksheet but the leftmost value is the combination of samenumber and some text I want the value in the 4th column. pls help

Hello, Loga,

To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

Dear Sir,

I Want to update leave code in attendence sheet according to leave trransaction on the basis of Employee code and respective dates from start date to end date remaining values in main sheet should be constant.

Dear Sir,

I am doing vlookup in my time sheet there is four sheets i have done 1 sheet only balance 3 sheets i can't find please help me.

Parameters:

Name Start Date End Date Value

A 01-Apr-11 02-Feb-12 2

A 03-Feb-12 01-Mar-12 3

A 02-Mar-12 31-Dec-13 4

A 01-Jan-14 31-Jan-14 5

B 09-Jan-13 04-Apr-14 6

B 05-Apr-14 07-Feb-15 7

B 08-Feb-15 01-May-16 8

B 02-May-16 01-Jun-16 9

Name Date Value

A 30-Apr-12

A 05-Feb-12

A 30-Jan-14

B 07-Apr-14

B 20-Feb-15

Please help for for the above value column based on provided parameters.

Hi,

I'm currently looking for a function, that helps me sum up the numbers corresponding to the duplicate data in a different worksheet and this value to be brought to another sheet by vlookup.. In simple terms, a function that sums up and vlookup.

I have an excel spreadsheet that I'm attempt to do a vlookup or index to get detail of the monthly cost for cell phone to the first tab of a worksheet.

I have a tab for the details of multiple cell phones by month. it includes base costs, total minutes, text message, GB used, and so on.

I would like the front tab to pull the current month data from the detail sheet so the data can be reviewed monthly.

So I have created a tab that has the phone number, then a drop down menu for the month, then I need it to pull the data for that cell for that month.

This is where I get lost. I can get the data for the first instance but when you change the date nothing happens. I would appreciate any help you can provide.

Hello, Lance,

To help you better, we need a small sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

Hi,

I have a Main table with many columns and rows. I have create a bill of matrials taking data from different columns. When I put a formula

=INDEX(B$4:$B$9,MATCH(F5,$A$4:$A$9,0),MATCH($G$3,$B$3:$D$3,0))/$B$10*$G$10

it also generate zero (0) values in rows data was not available.

My question is: I want to generate data with value in initial rows.

Kind regards,

Waseem

Hi,

if i have below column or rows,

upc article no description

8901725121112 10108458 furnish ang

is it possible if i want description by upc and by article no in one cell.

How to do two-way lookup in Excel??

in this you have showed how we could find second match result for the selected item only LEMON. which is very nice.

If suppose Same I need find for more than 20 items from the Raw data like more than 500 do I need to enter all 20 items individually or there is any short way for this.

Maria,

I have sent in the excel file that I'm developing.

how to advance xxl

I am using the following formula as an array, but am getting blanks. Can you help resolve this please.

=IF(ISERROR(INDEX(Table_ExternalData_1[#All],SMALL(IF((Table_ExternalData_1[UserName]=$B$1)*(Table_ExternalData_1[comm_datetime]>=$B$2)*(Table_ExternalData_1[comm_datetime]=$B$2)*(Table_ExternalData_1[comm_datetime]<=$C$2),ROW(Table_ExternalData_1[UserName])),ROW(1:1)),4))

Basically trying to retrieve data using 3 criterias.

Hello,

I'm trying to make a meal plan tracker and I'm having a difficult time adding a formula to it.

One whole sheet is a food list with name, amount, calories, carbs, and fats. The other sheet is where I'm putting the formula and the columns are food, serving, calories, protein, carbs, fats.

I want to be able to input my food column and have it pull the info from the other sheet.

I was doing it column by column, this is what I have so far but it won't work:

=vlookup(vlookup(a2,list!$a$1:$f$41,0,false),calories,0,false)

pretty much I want to type in chicken and matches chicken from the other sheet, and pulls the values over. Thanks!

I am trying to write a formula that if column B&C is empty it bring nothing. If C has something it must bring what is in column A and if B has something it must bring what is in column A.

A B C

L1125A110 L1132A117

L1130A031

L1130A066

L1140A026

L1145A019

L1145A031

L1145A043 LMD322641

L1145A049

L1145A051 LMD342154

L1145A067 LMD193875

L1145A070 LMD197622

L1200A147

L1200A312

L1211A021

L1211A039

L1226A003

L1230A040

L1230A046 L1230A154

L1250A010

L1250A015 LMD354664

"How to get all duplicate values in the lookup range" -- your information here is great! I need to know how to do the above using VLookup. Index and Match do not seem to work. I am using Excel 2013. This is my issue:

Column A has 1's and Null values; Column F has the Data that I need to copy.

I need All of the Data in Column F as long as Column A is not null.

Example

Column A Column F

cell1 = 1 "West"

cell2 = Null "East"

cell3 = 1 "West"

cell4 = 1 "East"

cell5 = 1 "South"

cell6 = Null "South"

Oh sorry, I am using VBA...

hi i'm trying to use vlookup to pull data from multiple sheets. i'm ok with vlookup within a sheet but how do i read a sheet name from a cell then go to that sheet and use vlookup normally. basically i have sheet with a list of other sheets in it this list changes from an outside data source so i need to read the sheet name and return some data from this sheet.

Hi Svet,

It's my first time to visit your site and it very helpful. I need your advise on how to make my project more efficient in a way. I'm creating a monthly scorecard that goals may vary from time to time and has multiple ranges to use. Sample data below:

Inbound

0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100% Weight

Attendance 80% 82% 86% 89% 90% 92% 93% 94% 95% 96% 98% 100% 15.0%

SO there's the month, range of score to get (fixed to 0% 15% 30% 45% 50% 65% 75% 85% 90% 95% 98% 100%) and the weight as the last column (say, if agent gets 90% in attendance, he gets 50% * the weight applied of 15%. That weighted score should be summed up with all other KPI weighted score to get the 100% computation for that month. Im sending out the file to your email. :)

Thanks in Advance!

Paul

Hi All,

I have one question please some one help me to fix it.

I have two tables one table is having only Serial numbers with removing duplicate

Another table serial numbers like 10 serial numbers in one column with other details like products suppliers resellers etc.

Here i want to perform vlookup for unique serial number table and get the supplier details to it.

please help me some one. thanks

Like this 10 serial numbers i have in some 250 and more than 250 characters length serial numbers column, i want supplier information from this sheet to the original sheet.

Example:-

Sheet one:-

Serial_numbers

Abc

DEF

GHI

JKL

Sheet two:-

Serial_numbers supplier

ABC,DEF,GHI Amazon

ABC,GHI Ebay

JKL,ABC Flipkart

I want the details like below

Serial_numbers supplier

Abc Amazon

DEF Amazon

GHI EBAY

JKL Flipkart.

Like this i have around 50k line items.

Hi All

Need your help regarding Vlookup, my date contains

Date

Product Name

Opening Stock

Issued Qty.

Bal. Qty.

I need to lookup for two criteria, e.g. data of specific date and product

Hi, can you help me with this.

*ship mode minus "H" the order priority "D"

These are the columns and the data are in words.

Info tab has all the numbers that corresponds to the H and D columns:

Order Priority

1 Low 3

2 High 5

3 Not Specified 0

4 Critical 7

Ship Mode

1 Regular Air 10

2 Delivery Truck 15

3 Express Air 7

What formula should I use and can you help me with this one?

Dear Sir,

I am using Microsoft excel 2007 I want to use vlookup formulla with 3 lacs column but it could not work, it shows error message "invalid reference and this file verson can not contain formulla that reference cell beyond a worksheet size of 236 column (column IW of higher) or 65536 rows".

I don't understand please help me what I do??????????

I have a excel file where I daily populate cash transactions for several bank accounts. I need to a daily rollfoward with beginning to eOD balances. I prepared a tab for each account and the date in column A and columns B thru J have the cash category. I performed a pivot table that has Account,date,category and total. I need to find a way to drop the data from each bank account into each tab I made so the rollforward can be updated by a simple refresh. I think this can be done using complex if then and combine Hlookup tables but I am not able to drop each dollar amount in its corresponding category by date

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

Working with this formula, but getting the #N/A error.

Material Material Description 8/1 7/25 7/18

105939 EBF Org Wild Arugula 2x2lb PL 574 1,350 21,321

105940 EBF Org Baby Spin - 2x2lb PL 1,741 1,345 1,564

2,315 2,695 22,885

Need to lookup a material (105940) and bring the qty. on a specific date (7/18). Does this not work because it's trying to reference dates?

Hello,

I have sales for about 1000 customers split by various services all grouped into one invoice by split by line item. I need to pull this information and insert the values of each service by customer in a table.

Source:

Client 1 Plumbing done on 11th $500

Client 1 Plumbing done on 24th $500

Client 1 Work done for Carpentry $1000

Client 2 Plumbing was done on 25th $1000

Client 2 Installation done on 10th $250

Client 2 Carpentry done on 10th $700

Client 2 Carpentry done on 10th $1700

I need this info in the below table format (Note, I need sum of each service for each client)

Clients, Plumbing, Carpentry,Installation

Client 1 $1000 $1000 0

Client 2 $1000 $2400 $250

The above information looks simple, but the description of each item is a sentence out of which I need to pick a "code word" to identify the service. Thank you so much for your help!

Can anybody help me with this:

example:

Col.A Column B

States Sales Rep Name

AZ, CA Blank

WA,ID,OR Blank

TX, OK, Blank

in column A, there's abbreviation of states, like CA, NV, AZ etc...

I need to conduct a Vlookup that searches the range of these states in (A) and cross references the master Table (on another sheet) to populate Column B. My challenge is that i can't find a Vlookup to search the range of 50 states. Any suggestions?

thank you

Dear sir,

If you please help me to solve the problem.

I have

(Sheet 1)

A=1

B=2

C=2

A=4

B=5

C=6

I want(Sheet 2)

Sum of A=?

B=?

C=?

I Will be highly oblized to you.

Regards

Pappu Pattanayak

Hi Pappu,

You can use below simple command

Think its in one sheet and result is also published in one sheet

=SUMIFS($B$:$B$,$A$:$A$,D1)

Hi There

I you can help. I am trying to get the below formula to work. It is bringing me back the Case values but N/A for anything else.

Essentially I have a code in a cell and I want to know which column this code corresponds from three columns of codes on another sheet.

This is the formula I have tried. I have tries nesting IF statements combined with vlookup statements. If at all possible I would like to have this all in one cell rather than spread out. Any help would be much appreciated.

=IF(A2=VLOOKUP(A2,Compare!H:H,1,FALSE),"Case",IF(A2=VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE),"Shrink","Item"))

I have also tried

=IF(VLOOKUP(A2,Compare!H:H,1,FALSE)=A2,"Case",IF(VLOOKUP(Sheet1!A2,Compare!G:G,1,FALSE)=A2,"Shrink","Item"))

very useful info you have there. thank you very much. but i could not make the formula work. can you please help me? i use vlookup for a specific text(column A) and data next cell (column B, col_index_num)) but when the text is in more than one cell, i need the different datas concatenated with space. Can you please type the formula?

if i have data like

code code no.

1001 2001 1

1002 3001 2

1003 1001 3

i want to lookup 1001 in different work book & no. infron of code

kindly help which formula to use

I've been working on a 2 criteria VLookup and have it working, but I need to know if there's a way to display the found data in the same format as it exists in the source table.

The columns I'm pulling data from may contain "$ values", or "% values".

(Ex: Where 'Name=Neal' and 'Month=Mar')

Name Jan Feb Mar Apr May Jun

Pete $26 $34 $33 $37 $41 $44

Sally $41 $29 $0 $49 $24 $34

Neal $42 $36 100% $38 $25 $37

Diana $40 $18 0% $38 $35 $32

I've tried using conditional formatting on the 'results' fields, to change the format based on value, but I can't figure out when "'$0' vs '0%'", 100% vs $100, etc.

I thought I could expand my vlookup to include the Cell() function to return the formatting, but don't know if this is the nest way.

Any thoughts?

Instead of images, if you can use actual tables for your examples, we can copy and experiment in our Excel environment. Thank you!

hi,

if i want the formula return in column instead of row

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

No response needed ... this is just a complement.

"This web site is remarkable!!! I had always wondered how to go about solving some of the more complex problems I face at work; I can now say that I can, thanks to this web site. Thank you so much !!!! Alex."

Hi,

I have a 2 column table (Wards and teams) to know what team does roadworks in what ward.

Some roadworks are in multiple wards however, needing several teams.

In this event, the column 'wards' in another table (called roadworks) would show each ward separated with a '/'. Some ward names have spaces in them.

How would I do a Vlookup that shows me all the teams that should be contacted for a roadworks' job?

Good Lesson

How to apply double vlookup in single cell.is there any possibility.

How to use VLOOKUP to get maximum value

i have 2 worksheets

sheet1( call it a data sheet)

columnA columnB

name marks

robert 2

robert 3

robert 4

charles 2

charles 5

charles 6

i want to write a vlookup or a suitable formula in sheet2 which will pull max marks from sheet1 and get the results

for e.g.if i type robert it will dispaly 4 as the answer( maximum value )

sheet2 ( formula sheet)

columnA columnB

name max_marks

robert 4

charles 6

any help!

Hi

I'm trying to get all duplicate values in the lookup range from pivot table report into manual customised report.

1. Pivot table worksheet have multiple duplicate names in the first column with various values in the eighth column.

2. Summary worksheet have manual customised report layout with merged rows of all the names which are in the pivot table report.

3.I would like to display all the values of those duplicate names from the pivot table to the manual summary worksheet report.

Is that possible, please give me the right formula.

I tried {=IFERROR(INDEX($K$7:$K$60,SMALL(IF($P$5=D7:D60,ROW(K7:K60),""),ROW()-3)),"")}, but it shows no values.

please help.

thanks in advance

Try This ROWS($E15:E15)),2) instead of this ROW()-3)),"")

($E15:E15)) is a resulting value, where you want your result,,,

If your answer value is in 2nd coloumn then type 2, if it is in 4th coloum then type 4 and close bracket

HI I have a 3 page workbook I have built and on all is number to be referenced in column A sheets 1 - 3. I have been sent another workbook that has the same numbers in column A. I need to in column B on the one sent in to reference the number in column A and locate that single occurrence of that number on My 3 pager and input the corresponding data from each column N on my 3 pager. I assume I will need 3 Vlookups but how do I lay that out in the Cell B on the one sent ?

Thank you so much for this useful tips. Really reducing my work by multiple folds.

Regards,

Aravind

I'm having difficulty getting one of your examples to fit my scenario. I have 2 tables. Each includes the person's name and a date. I want to match the name / date combination in one table with the same combination in the other table, then return a third field.

In one table, I have dates across the top (column headings) and names down the side (row headings). I want to match the date / name combination from that table to the date / name combination in another table (see below) and return another field, in this case the city.

10/9/16 Bob Boston

10/16/16 Phil Philadelphia

11/13/16 Pete New York

Any help would be appreciated.

Thank You.

In your section “How to get all duplicate values in the lookup range” you explain how to use Index, Small and Row to generate the list of values. I think there is a simpler way. Try the following. Select the range F4:F10 or a range with enough rows to hold the maximum number of expected values. Enter the following in the formula bar and then press SHIFT-CTRL-ENTER

=IF(B2:B16=F2,C2:C16,"")

I hope this works. I would like some help too however. I would like to make the list created by the above array formula to appear in the dropdown box for the allowed values for another cell i.e. using the Excel Data Validation functionality. However, I cannot get this to work. It seems as if array functions cannot be entered into the Data Validation entry box. Do you have any suggestions?

Ian

plz clarify following senario,

Table _ 01

Compay No of Employees

aaa 221

aab 245

aac 250

aad 290

aae 271

aaf 340

aag 193

aah 213

aai 90

Table _ 02

Compay No of Employees

aaa 196

aai 345

aab 650

aag 193

aae 278

Expected result

Table _ 03

Compay No of Employees

aaa 196

aab 650

aac 650

aad 290

aae 278

aaf 340

aag 193

aah 213

aai 345

I used formula =VLOOKUP(D4,A$17:B$21,2,0) and ends up with following results

Table _ 04

Compay No of Employees

aaa 196

aab 650

aac #N/A

aad #N/A

aae 278

aaf #N/A

aag 193

aah #N/A

aai 345

how can i obtain correct results

NB:

i need to update table 01 referring table 02 and if data entry is not available in table 02 the data entry in table 01 should be remain as it is.

appreciate your kind response thanks and regard

Hello,

Try to write a formula with if and vlookup and I think I got it to work but I'm having trouble making to work for a entire year I need.

Example:

Sheet 1

October November

Names Hours Hours

John Smith 25 15

Katie Wilson 45 55

Jeremy Pate 37 20

Sheet 2

October

Hours

Names

John Smith

Katie Wilson

Jeremy Pate

=IF($B$1='Sheet1'!$B$1,VLOOKUP(A1,'Sheet1'!A:K,2,0),"no")

With this formula if B1(October) in sheet1 than it matches and the formula shows me the hours. However for November unless I change the formula I can't get the hours to appear in Sheet 2. Sheet 2 has ONLY one column for hours and I can't add anymore due to reporting restrictions. How do I get excel to automatically update it self if change "B1" Cell from October to November.

Please help. Thank you

Sara, I think your problem would be solved using more of a 2 dimensional vlookup: https://exceljet.net/formula/two-way-lookup-with-vlookup Please note that you need to follow the advice left by Thyagarajan in the comments and add 1 to the column match to get it to line up properly. Hope this helps.

Sara, please read the section above entitled "How to do two-way lookup in Excel". I think this is what you need to implement.

Hi, i need ur assistance to get the result using formulas especially using vlookup. Example i have a large data. In a column i have a account numbers with different branches. Also few account numbers are same but different branches. Is it possible to get the result using vlookup with same account number with different branches from the large data. Please help me...

And i would like to say thank you for providing the detailed functions of formulas. It is really helpful for me.

Use

=vlookup(a2&b2,data,3,false)

Excel VBA VLOOKUP successfully returns matching data AND also returns an Err.Number = 1004. The datum are dates. I am suspecting the data types as being the issue. Since the VLOOKUP is successful it does not present a problem but, it is puzzling and I would like to be certain there is not underlying issues.

Why would a successful VLOOKUP throw a false error?

I applied vlookup multiple rows with the same lookup value with index farmulla, my same lookup values are more than 3 i.e. if it if 10, then it shows only 1st value as a result and then #num error. rest of 9 values are not shows. i copied the farmulla 10 times... but results not came.

pls help me with this erros

HI,

I am trying to easily portray an inventory availability list that instead of using style and size codes, simply indicates if a a style has the sizes in stock.

For example. Item: MD195, has sizes XS, S, M, L, XL. however, each size has a specific #, XS = MD195-01-31, S = MD195-01-32, etc.

I am trying to get it to update every time i load the current inventory, if it is available in XS, put yes or no, Small yes or no, etc.

The real difficulty comes when I try to drag the formula to apply to all cells, while referencing the appropriate cell(s).

Hi,

Thanks for creating this site. Definetely the most detailed, practical and useful information.

In the section "How to use VLOOKUP to get 2nd, 3rd, 4th, etc. matching values", I understood the way to find the 2nd occurence. But, could you also share the way to get the 3rd or nth occurence?

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.

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..

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

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

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

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.

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!!

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

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

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

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.

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

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

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

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,

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!

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

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

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.

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

i have a workbook, id number wise i entered their certificate expiry.

ex:

1:15/10/2016

2:15/10/2016

1:14/10/2017

2:14/10/2017

1:13/10/2018

2:13/10/2018

i want result ("what is the validity of 1(which means ans is 13/10/2018)).

how can find this result?

Is there a way to parse a column, like column A for example that has 4000 names. On another tab there are 50 names

The first tab has 12 columns full of data but I only need 50 of those 4000 with the data

Instead of hand picking each one and copy paste

Is there a way to look at that source data, if name exist, add to "array" and then create a new tab with these 50 people and every column to the right?

How to get the smallest value in all occurrences of lookup value (duplicates)

I want to look through 15 tabs of a workbook and see in which of these tabs there is a value and depending on the which tab is the last tab that it was located at, insert to a cell a date. is this possible with a function or should I search for a VBA code?

How use in vlookup in two sheets

Hi Anil,

To do Vlookup from a different Excel sheet, you simply supply the sheet's name followed by an exclamation mark in the table_array argument, e.g.

=VLOOKUP("text", Sheet2!A2:B15,2)

For more information, please see How to Vlookup from another worksheet.

If you want to perform sequential Vlookups in different sheets based on whether a previous Vlookup found the lookup value or not, you can nest Vlookup into IFERROR and use two or more such nested functions within one formula, like shown in this example: How to do sequential Vlookups in Excel.

Hi,

Your advice is appreciated as I am trying to fill in the ASSIGNED QTY in table1 from the table2 based on CUSTOMER NO. & ITEM.

table1:

ITEM CUSTOMER NO. ASSIGNED QTY

10001653 50023243 ???

10001656 50022603 ???

table2:

NO NAME 10001653 10001656

50023243 cust1 5 10

50022603 cust2 30 35

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

iam preparing score card of students which includes semester 1 and semester 2 is there any formula to get the result in one cell by using vlookup formula or any other formulas???

For eg if i click semester 1 i need to get the result of semester 1 only.if i click semster 2 i need to get the result of semester two only in a single cell?? Can any one pls help me???

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Dear Sir,

I Just Want to Transpose all related columns to a single Row.

Ex-

ICD QTY Invoice No. Vehicle No.

CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195

CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195

CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196

CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196

Result Required as

ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No. ICD QTY (Nos). Invoice No. Vehicle No.

CR0103024080 106.00 SI/17-18/0011 RJ 06 GB 3195 CR0103024080 6.00 SI/17-18/0012 RJ 06 GB 3195 CR0103024080 8.00 SI/17-18/0013 RJ 06 GB 3196 CR0103024080 4.00 SI/17-18/0014 RJ 06 GB 3196

Hello,

I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.

However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.

Sorry I can't assist you better.

Hello,

I have multiple file for Raw data and one main file. like below. I need to know the last name of all EMP ID from all files to main file. How can i get this in single formula, by combined the all data in one file i can get that but its time taking. can i get this without combined the data in one file?

thanks in advance.

Below is the sample for data.

file 1.

EMP ID last name first name

101 yadav naveen

102 kumar deepak

103 patel gaurav

104 sharma vivek

105 Ghosh jay

File 2.

EMP ID last name first name

101 yadav naveen

200 kumar deepak

201 patel gaurav

203 sharma vivek

main file.

EMP ID last name

101 ?

102 ?

103 ?

104 ?

105 ?

108 ?

200 ?

201 ?

202 ?

203 ?

.

Hello,

Please try to solve your task with the help of the Combine Sheets tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.

After you install the product, you will find Combine Sheets in the Merge section under the Ablebits Data tab.

Hope this will help you with your task.

Hi, i have a table with 50rows (drugs) and 30 columns (Citties) and sales qty of drugs are spread for each Citty. I need that this table of data returns in 3 columns named : City , Drugs,Sales Qty.

How can i do this ?

Br.Odi

Hello,

Please try to solve your task with the help of the Unpivot Table tool which is a part of our Ultimate Suite for Excel. You can download its fully functional 14-day trial version using this direct link.

After you install the product, you will find Unpivot Table in the Transform section under the Ablebits Tools tab.

Hope this will help you with your task.

Thank you for this article! It was immensely and has worked quite well.

The only issue I am having is that it is retaining the spaces between cells with the output...for example, in your sample above for all occurrences of lookup value (duplicates), Dan Brown’s Biscuits and Dan Brown’s Cherries have 6 rows between them...for my output, these rows remain but are blank/#NA...any good ideas as to why this may be?!

Thanks any and all for your insights!

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

Dear Sir/madam

I tried if condition with vlookup.I got the required result for few cases.Though the required data is there in the next sheet i couldn't get that.How can i get that for remaining cases.

I have an tool inventory check out sheet that references a table where there is equipment type column, a description column, followed by an ID number column. I am looking for a formula that after the equipment type is determined, the descriptions are limited to that equipment type,and then the equipment ID number.

Hello,

For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.

Please also don't forget to include the link to this comment into your email.

I'll look into your task and try to help.

As you have explained this formula returns the 2nd instance:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

Where do I edit the formula to return the the 3rd 4th 5th etc

Thanks in advance...

Hi i am very excited about using formulas i excel after reading this page. please help me to solve the problem that i have... i have set of numbers related to my field.. for instance.,

spool no j.no fit up no

16011828-2 6

16014266-1 1OSW

16010115-1 2

16011586-1 2

14010644-1 7

21060012-1 17 24865 / 24842

14060009-1 4

12030178-1 4

14010630-1 4FFW

16011442-1 1

16011925-1 3FFW

16011815-1 2

14010914-2 6

16011440-1 6

12010688-1 2

14010581-3 13

14010088-1 1

14010619-1 1FFW

16090078-1 6

17010191-3 9

12011083-3 7 04574 / 4555

16014313-1 3FFW 21761 / 21766

17010135-2 7

14010658-1 1

16011816-1 4

14010643-2 8FFW

14011316-2 3

12010579-2 7

14010089-1 4

16030591-4 10

16011263-2 13FFW

17010945-2 12

16010822-2 7

12010632-2 7

16090630-3 8FFW

14010659-2 5

16011165-1 4 24199

14010500-2 12

16011829-1 2

12010644-2 13

12010631-1 5

17010205-2 8FFW

16011167-1 3

14010581-1 1FFW

14010660-3 8FFW

16013899-1 2

12010689-2 5

16011842-1 1FFW

16011730-1 6

17010208-3 7

12010551-2 13

17010176-1 5FFW

17010140-1 2

14010676-1 5FFW

16011898-2 12

14010623-2 8

12010688-1 1

17010147-2 11

16013031-2 17

12010641-3 10

12060018-1 2

14010677-2 8

12010553-2 10

12010057-2 14

14011050-2 10

16011852-1 8

17010301-1 1FW

14030082-1 3FFW

16011167-3 11

16011166-2 6

14010498-3 10

like this,, i want to get all the occurrences of my selected spool no,

=IFERROR(INDEX($C$2:$C$63399,SMALL(IF($F$2=B2:B63399,ROW(C2:C63399)-1,""),ROW()-3)),"") I can use the formula,, but i cannot use it continuously,, after finishing one set of calculation..this formula for only one set of calculation,,, and i want to use the array in row vise(spool no only). not column wise,, please any one help me to fix the problem.. thank u..

One can use the VLOOKUP function with 2 criterias WITHOUT any Helper-Column.

It will become an Array-Formula with some concatenations BUT within the formula.

*** My solution is presented in the below linked picture: https://postimg.org/image/mdmfjj7gd/

----------------------------

Michael (Micky) Avidan

“Microsoft® Answers" - Wiki author & Forums Moderator “Microsoft®” Excel MVP – Excel (2009-2018) ISRAEL

MAS Code 23% 18% 15% 12.50% 10% 7.50% 5% 5%

10100 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10

10101 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10

10102 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10

10103 0.29 0.21 0.19 0.18 0.17 0.16 0.14 0.10

i need to pull the commission based on the price that is selected from dropdown list. I dont know how to do that.

The first column is the item number

I am having SKu data in one sheet and i have pan india sku data in othr sheet , actully i want find out that first sheet sku qty. Location wise in first sheet against each sku.pls help

Hi, i am using the following VLOOKUP comment which works great! and really could do with this working in Hlookup however it doesnt work, are there different parameters for Hlookup?

Vlookup

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant

‘Updateby Extendoffice

Application.Volatile

Dim xRet As Variant ‘could be an error

Dim xCell As Range

xRet = Application.Match(LookVal, FTable.Columns(1), FType)

If IsError(xRet) Then

VlookupComment = “Not Found”

Else

Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)

VlookupComment = xCell.Value

With Application.Caller

If Not .Comment Is Nothing Then

.Comment.Delete

End If

If Not xCell.Comment Is Nothing Then

.AddComment xCell.Comment.Text

End If

End With

End If

End Function

Hlookup

Function HlookupComment(LookVal As Variant, FTable As Range, FRow As Long, FType As Long) As Variant

‘Updateby Extendoffice

Application.Volatile

Dim xRet As Variant ‘could be an error

Dim xCell As Range

xRet = Application.Match(LookVal, FTable.Rows(1), FType)

If IsError(xRet) Then

HlookupComment = “Not Found”

Else

Set xCell = FTable.Rows(FRow).Cells(1)(xRet)

HlookupComment = xCell.Value

With Application.Caller

If Not .Comment Is Nothing Then

.Comment.Delete

End If

If Not xCell.Comment Is Nothing Then

.AddComment xCell.Comment.Text

End If

End With

End If

End Function

Any help would be greatly appreciated 🙂