Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to use than nested IF functions. You can also use the methods described in this article to calculate commissions, tariffs, charges, […]

## Archive for the ‘Vlookup’ Category

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP function is much easier to use than nested IF functions. You can also use the methods described in this article to calculate commissions, tariffs, charges, […]

Kamran Mumtaz asked: Is there any way to use VLOOKUP for multiple criteria and I do not want to use CSE?; My answer: No, not to my knowledge Kamran Mumtaz: This is the formula given by Aladin Akyurek without (CSE)... =INDEX(Sheet3!$B$2:$B$65, […]

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted dates for Pen, Paper, and Eraser. I then had locations substituted for $ values.Where I have a date of say, […]

Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down list VLOOKUP and two conditions (date range) INDEX and MATCH VLOOKUP and a condition The animated picture above shows you […]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When you click the cell that contains the HYPERLINK function, Excel goes to that location in the table. Formula in cell […]

It can be really hard trying to follow a lookup in related tables. I will show you how to use conditional formatting for easy identification. If you add more rows to the tables, the conditional formatting expands automatically. Conditional formatting formula applied […]

The "new" excel 2010 powerpivot feature and DAX formulas lets you work with multiple tables of data. You can connect tables to each other by relationships. When relationships are made nothing stops you from doing lookups to related values and […]

Table of contents Lookup multiple values in different columns and return a single value Lookup multiple values in different columns and return multiple values Lookup multiple values in different columns and return a single value S.Babu asks: Dear Oscar, I […]

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles "ON Work" The drop down menu is in cell B50 I then need to be able to look up whatever […]

In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and populate a combo box (form control) Copy selected combo box value to a cell Refresh combo box using change events […]

This post describes how to search visible values and return multiple values from a table. Some rows are hidden because of table filters. I am not using the vlookup function in this formula. Example, Array Formula in cell B14: =INDEX($C$2:$C$9, SMALL(IF($B$11=(IF(SUBTOTAL(3, […]

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. opposite the name of customers are OR No. issued to various customers. OR No. is in broken sequence. My question […]

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and sheet2 contains table 2. The search value is Pen and is in cell B9. Array Formula in cell C9: =IFERROR(INDEX(tbl_1, […]

wackyboy asks: My database is as shown, where I have company abc sells siemens, omron and mitsubishi and company qwe sells omron n siemens. Company Products abc Siemens Omron Mitsubishi qwe Omron Siemens asd Omron Moeller zxc Mitsubishi Omron So […]

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? my sheet is setup as follows A B C D E 1 Section Category item flavor size 2 food Coffee Espresso none Single 3 food […]

In this post I will describe a basic user defined function with better search functionality than the array formula in this post: Fuzzy vlookup. The user defined function searches for a cell with as many characters matching as possible. It is […]

The array formula in this blog article has no "Fuzzy logic" nor vlookup function. But it can return names or words arranged differently and with minor misspellings just like a user defined function with "Fuzzy logic". There are too many […]

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

Array formula in D4: =INDEX($A$1:$A$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + SHIFT + ENTER. Copy cell D4 and paste it down as far as needed. Array formula in E4: =INDEX($B$1:$B$7, SMALL(IF(EXACT($A$1:$A$7, $E$1), ROW($A$1:$A$7), ""), ROW(A1))) + CTRL + […]

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above but it didn't work for me and I can't figure out how to adjust it to accomodate my needs. Here […]

In this post we are going to extract multiple text values. We are looking for names and the criteria are two or more states (cell B18:B19) and two or more months (cell D18:D19). But first there is something we can […]

I found a question in the comments section. You can find the question in this post: Lookup values in a range using two or more criteria and return multiple matches in excel Question: Unfortunately, I can't make enough sense of […]

Question: Here is the problem: i have a data table with 2 columns: A B 2.93 12.8 2.94 12.2 3 8.38 3.03 6.76 3.04 5.33 3.06 6.36 Lets say i have a cell with number 3. I need to find […]

Question: Does anyone know how to do a vlookup of three columns to pull a single record? Answer: I am guessing that you want to lookup a value in any of three columns to pull a single record. In this […]

In a previous blog post Sum adjacent values using multiple lookup text values in a column in excel I created a formula to sum specific values using two or more conditions in a column. In this blog post I will […]

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using one criterion and return (if possible) multiple adjacent values. In this blog post I´ll […]

Here is a follow up to this previous post: Vlookup with 2 or more lookup criteria and return multiple matches in excel Sum search criteria price values Cell references in array formula in B14: =SUM(IF(COUNTIF(B10:B11, B3:B7)>0, C3:C7)) + CTRL + […]

In previous posts I provided formulas on how to lookup one value in a list and return multiple matches. Using array formula to look up multiple values in a list How to return multiple values using vlookup in excel Search […]

Question: Hi, The formula here works great but I can't figure out how to change it to work with data in columns. Here is what I have: =INDEX(A2:E2,SMALL(IF(A1:E1=A3,COLUMN(A1:E1),""),COLUMN())) A B C D E 1 A B A C D 2 […]

The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches. Although VLOOKUP is not used in these array formulas, they are easier to […]