### Online Excel Course:

## Lookup Situations in Excel.

We hope you enjoy our Q&A Online Excel Course /Reference Guide on Look-up situations you might encounter using Microsoft Excel. This short excel online training course will offer possible solutions to many of these situations that might arise . See if you can work out a solution and then check it against ours.

**Target users:**

This question and answer **online excel training course** will be suitable for those who have learnt how to create basic Vlookup, Index and Match functions. If some solutions require knowledge of other functions or techniques, these will be clearly outlined as will the difficulty level of each solution.

This online excel training course can also be used as a practical examples & solutions complement our online excel course- **WorkPlace Excel**.

**Objectives of this Online Excel Course:**

To deepen your knowledge of looking up values in Excel from lists and tables in many various circumstances. Also to act a future reference point if you come across any of these specific problems in the future. Look out for our upcoming article on the new XLOOKUP function , which will cause quiet a stir.

**Time Required.**

This online excel course on the Excel Look up situations will take 2-3 hours.

#### Online Excel Course Contents.

- When adding a new record to my lookup table, I am getting an #N/A Error?
- I don’t want my Vlookup to return #N/A errors but another messsage?
- Can’t use a Vlookup function , as I need to search from right to Left ?
- How to Look up Multiple Index columns in a list?
- How to Look up a Value based on Column and Row ?
- Looking-up a number with decimal places but from right to left in your list?
- How to Look up a combined Text and Number column?
- How to Look up a Text and Number columns in Excel using Arrays?
- How do I lookup text that is case sensitive?
- How to lookup text that is case sensitive using Sumproduct?
- How to search for text containing certain characters using the Lookup functions?
- How to look up a Date using the Vlookup Function?

### When adding a new record to my lookup table, I am getting an #N/A Error?

Basic level: Knowledge of Vlookup required.

Let’s start off our online excel course on the Vlookup function by solving a common problem that often occurs with this function. Look at the example below.

My lookup table is F2:G7. This is a table of product codes and what each code stands for. In the B column, I need Excel to tell me what each of the product codes in in A2:A8 stands for. To achieve this I created the following VLOOKUP Function in cell B2.

**=VLOOKUP(A2,$F$1:$G$7,2,FALSE)**

But then, a new product code has been created – code-106 , so I add it to my Lookup table in F8 and G8.

But yet when I type 106 into A9 and copy the down the VLOOKUP Function into B9, I am getting the #N/A.

How can I fix this problem. You have the following solutions.

- Redefine your lookup table in the VLOOKUP Function, to $F$1:$G$8
- Insert the new row for 106 product details
**within**your lookup table. This shifts the remaining items down and automatically expands the Vlookup range. - Specify F:G as the lookup table. This uses the whole column as the lookup table. Use this method with caution.
- Use an Excel table to hold your data. This would be by far our preferred solution

** I don’t want my Vlookup to return #N/A errors but another messsage?**

Basic level but requires the use of the IFERROR Function

Often the appearance of #N/A errors makes a workbook appear as if it has lots of unintentional errors. But your workbook will look more professional if we could replace these error symbols with some other term.

This is how we do this.

IFERROR(VLOOKUP(Value, Dataset,Column No,FALSE), “- “)

The IFERROR Function is great for this. If the Vlookup function can return a correct value it will, but if it is returning an error like the N/A error, the second argument of the function is outputted instead. In this case , it will show the – symbol.

But it is important , that you make sure that your Vlookup function is working correctly before you place it inside the IFERROR Function as you don’t want it to hide any real errors.

## Can’t use a Vlookup function , as I need to search from right to Left ?

** Advanced level:** Knowledge of Index & Match Functions.

This Q&A in our Online Excel Course you will soon come across as you get heavier into using Microsoft Excel.

The VLOOKUP Function search the left most column of your defined table and bring back values to the right of it. But what can you do , if the value you are searching is , for example, in the middle of your table and you want to bring back data to the left of it.

In these situations we need to use the INDEX and MATCH Functions.

**An Example:**

We have data in the Worksheet called ‘Data” that we want to look up and bring back an attribute from a specific record. See screenshots below.

For example, In Cell G20, by inputting an ID, we want to bring back the Name and phone number for that ID. But look at the first screenshot below and notice that the user names are to the right of the ID numbers. So we cant use a Vlookup function. We will get around this by using the INDEX AND MATCH Function.

The formula will be

**=INDEX(Data!$A$16:$E$27,MATCH(G20,Data!$B$17:$B$27,0),1)**

We know the table we want to search– Data!$A$16:$E$27 . We know that the name we are searching for will be somewhere in the first column. The unknown factor is what row the record we are searching is on. Thus we can use the MATCH Function, which returns the relative row position of that record.

Note that the MATCH FUNCTION can only search a column not a table. Give it a Table as the second argument, and it will return an error.

## How to Look up Multiple Index columns in a list?

**Very Advanced level:** Knowledge of Array formula, Index & Match Functions required.

Lets look at an example to see this more clearly. Here is our sample dataset.

Our problem is that we need to be able to search for both the Item Number and the quantity ordered , to return the Price Break, as show below.

The solution to this requires an array Formula .

**{=INDEX(Data9!$A$2:$C$7,MATCH(1,(Data9!$A$2:$A$7=B21)*(Data9!$B$2:$B$7>=C21),0),3)}**

MATCH(1,(Data9!$A$2:$A$7=B21) causes the creation of a temporary array of {F023,FO23,F023,FO23,F023,FO23,F023} . This is evaluated against the value in cell reference B21 which results in an arrays of {True…..True}.

This is multiplied by an second array which is {10 30 60 120 180 240}<=C21 which depending on the value of B21 could end up as

Match(1,{1;1;1;0;0;0;0}

Remember in Excel False =0 and True=1

The MATCH Functions then finds the location of the first 1 and this is then fed into the INDEX function.

## How to Look up a Value based on Column and Row ?

** Advanced level:** Knowledge of Index & Match Functions required.

Lets look at an example.

We want to be able to input a date into Cell B20 above and also input a time into Cell 20,and have a resultant value from the dataset below show up in cell D20.

But there is a complication, as you can see from looking at our list below.

If we were doing this manually using our fingers, we would be searching across row 1 for the date and then down t column A for the time, in order to return the intersecting cell.

The formula we will use in cell reference D20 will be

**=INDEX(Data8!$A$1:$F$12,MATCH(C20,Data8!$A$1:$A$12,0),MATCH(B20,Data8!$A$1:$F$1,0))**

**
**This simply uses two Match Functions to find both the Row and Column data for the INDEX Function.

So for example

**MATCH(C20,Data8!$A$1:$A$12,0)**

This function is saying ..Look for the value in cell C20, which is 9:00 and search for it in the single range A1:A12 and for that value exactly and if you find it, tell me its relative row postion. This is repeated for the date and both relative row positions now fill the index function.

## Looking-up a number with decimal places but from right to left in your list?

** Very Advanced level:** Knowledge of Array formula, Index & Match and Round Functions required.

This Question and Answer in our online excel course is actually quiet advanced.

Here is an example of this situation.

When we type in an order amount like 456.32 in cell B20, we want its order Number to be shown in cell C20.

Now from looking at the dataset above, there is an added complication – the order amount in column E and the order number is in column A. Thus we will be searching from right to left and can’t therefore use the Vlookup function directly in the solution.

We will need to use the INDEX and MATCH Functions so the formula we have used in cell C20 is

**{=INDEX(Data5!$A$2:$E$14,MATCH(B20,ROUND(Data5!E2:E14,2),0),1)}**

We also need the the ROUND Function in this formula. As you will have learnt in our WorkPlace Online Excel Course, the ROUND function rounds a number to a specific number of digits.

We use Round on the numbers in the data range E2:E14 so that 456.32335 becomes 456.32 etc etc

Also note that since we need to find the Round of all the values in the range E2:E14, we will need to use an ARRAY Formula. This as we have seen from previous example above , will create a series of 1 and 0 or true or false

Look out for these important points.

- Use .025 instead of the 2.5%

2. Remember the number you see on the screen is not necessarily the number Excel deals with. e.g. 4.14 might be 4.139

3. Array formula must be entered by using CTRL+SHF+Enter

## How to Look up a combined Text and Number column?

** Intermediate level:** Vlookup , helper column and the Concatenate Function

We want to Look up a Text and a Number from two index columns. In this example in our online excel course, we will use a very useful technique called ‘Helper Columns”

Lets look at an example:

We want to be able to input a customer name in B20 and their order No In C20.

Then we want to bring back the amount for that order.

This is a common situation in Excel especially since the Lookup functions cannot handle duplicate values.

if there are two ‘Luis o’Connell’, it will only find the first occurrence of it.

The data set we will use is

To use this method, we create a add a new column to our data table . We have done this in the range A2:A12 , as above , using the

formula **=B2&C2** and then copying it down.

Then we can use the VLOOKUP formula

**=VLOOKUP(B20&C20,Data4!$A$2:$F$12,6,0)**

Also note instead of using the & operator , we can use the Concatenate Function instead

** =VLOOKUP(CONCATENATE(B20,C20),Data4!$A$2:$F$12,6,0)**

## How to Look up a Text and Number columns in Excel using Arrays?

** Very Advanced level:** Knowledge of Array formula, Index & Match and also the SumProduct function (optional) required.

This next Q&A in our online excel training course is also quiet advanced as it again uses array formulas and also can use the Sumproduct function as another possible solution.

In this example, we want to be able to input a customer name like Mary Kate Ryan into cell B34 and have her Order Number to be shown up in cell C34. Finally we want the amount for that order to show up in cell D34.

A sample of the dataset containing the data is here.

This is a common problem that occurs in Excel especially also as the look up functions cannot handle duplicate values.

If there are two ‘Mary Kate Ryans’, they will only find the first occurrence of it.

The formula we will use in D34 is

**{=INDEX(Data4!$A$16:$E$26,MATCH(1,(Data4!$A$16:$A$26=B34)*(Data4!$B$16:$B$26=C34),0),5)}**

As an array formula ,this formula is based on creating a series of TRUES and FALSES in Data4!$A$16:$A$26=B34 and in Data4!$B$16:$B$26=C34

This create two series of 1 and 0 ranges. Then a True by True or 1 * 1 creates a 1 which the MATCH Function then searches for.

Once it finds the relative Row Position of this 1,it feeds this into the Index Function which then returns the correct attribute of the record been looked for.

**Alternative method using the SUMPRODUCT :
**

You could use the SUMPRODUCT Function also. If we assume the customer’s name is inputed into B27, then we could use the following formula.

If you use or have used our Workplace online excel course , the Sumproduct function module shows how it also creates a array of values -1 or 0

**=SUMPRODUCT((Data4!$A$16:$A$26=B27)*(Data4!$B$16:$B$26=C27)*Data4!$E$16:$E$26)**

## How do I lookup text that is case sensitive?

** Very Advanced level:** Knowledge of Array formulas and the Exact Function required.

This next Q&A in our excel course online is also quiet advanced as it uses array formulas again.

We want to look up text that is case sensitive. We can’t use VLOOKUP , Index & Match because they are not case sensitive. The solution requires the use of arrays.You may need to read our module from our ** online excel courses ** on ‘ Array Formulae’ or one from YouTube on array formulas to understand this task.

Let’s look at an example:

We want to search for **K**elly and then **k**elly without a capital k as shown below.

Here is the dataset we will be looking up:

The formula in C20 is

**{=INDEX(Data3!$A$2:$C$7,MATCH(TRUE,EXACT(Data3!$B$2:$B$8,B20),0),1)}**

What is going on in this formula. We are going to use an array formula and the Exact Function to lookup case sensitive text. The EXACT function checks whether two text strings are exactly the same. It returns True or False and **is case sensitive**.

The Exact function will compare the value in B20 to the values in B2:B8 in our list . Sine it is an array formula it will return a series of False values and one True.

Then the Match function will search for the True and when it finds the True it will return it’s relative row position. This is then used in the INDEX Function to return the correct attribute of the record. Remember when entering an array formula, **use Ctrl+Shift+Enter**

## How to lookup text that is case sensitive using Sumproduct?

** Advanced level:** Knowledge of the SumProduct function.

This next Q&A in our online excel course is also quiet advanced as it uses the Sumproduct function

We want to look up text that is case sensitive. We can’t use VLOOKUP because it is not case sensitive.

We will use the SUMPRODUCT Function to achieve our task.

**Let’s look at an example: **

We want to search for **K**elly and then **k**elly without a capital k as shown below.

Here is the dataset we will be looking up:

The Formula we are using in cell H20 is:

**=SUMPRODUCT((EXACT(Data3!$B$2:$B$7,G20))*Data3!$C$2:$C$7)**

How does this formula work?

The EXACT function checks whether two text strings are exactly the same. It returns True or False and **is case sensitive**.

The Exact function will compare the value in G20 to the values in B2:B8 in our dataset This will return a series of 0 ‘s and one 1.

Thus we are left with an array of True and False values multiplied by the array of C2:C7. As in all array situations, the first element of the first array acts against the first element of the second array, then the second element against the second, etc.

So we are left with a series of True and false values or 0 and 1. And from this the mathematical calculations are made – 0 times any value is 0 and 1 times any value is that value.

## How to search for text containing certain characters using the Lookup functions?

** Intermediate level:** Knowledge of Vlookup function and the & operator.

This Q&A in our online excel course is very a common problem that often arises when using Excel.

So this is the situation. We want to look up records that Begins/Ends/Contains certain text. So cell B20 is our input cell. Cell C20 will bring back the passenger name that contains the letters ‘ah’. D20 will bring back that customer’s number of bags .

The list that is been searched is

The formula in cell C20 is

## **=VLOOKUP(“*”&B20&”*”,Data2!$A$2:$D$11,1,0) **

It uses the & text operator . This joins text together. We use it with the * symbol, which stand for any series of characters or you could hardcode it with * beginingletters and end letters or use A5 if you put beginingletters *endletters into cell A5. The choice is yours.

Thus the VLOOKUP Formula is saying find a piece of text that contains the letters ‘ah’ that has others letters before and after it, in the table located at A2:D11 .Then return the attribute in the first relative column of that table and by the way, only find an exact match (the final ’0′ argument)

The Vlookup function to bring back the number of bags is

**=VLOOKUP(“*”&B20&”*”,Data2!$A$2:$D$11,4,0)**

## How to look up a Date using the Vlookup Function?

** Intermediate level:** Knowledge of Vlookup and Date function and the & operator.

This Q&A in our excel course online is also a very common problem. What should you look out for when searching for a date when using the Vlookup function or the other Lookup functions.

In the example below, dates are associated with a phrase numbers so when we inputted a date into Cell B26 , we want the phrase it is associated with to come up in cell C26

The Vlookup we will use in C26 is

**VLOOKUP(B26, Data!H1:J12,2,0)**

where H1:J12 is the range that contains dates and their phrase numbers.

The important point to note is that we **do not type in the date directly into the function**. typing in 12/10/2010 or “2/10/2010″ will not work.

Either use a cell reference ,like B26 , as we have done or you can use the Date Function. So this formula is also correct

**VLOOKUP( DATE(2010,10,12), Data!H1:J12,2,0)**

Do not use dates directly in formulas. 12/10/2010 or “2/10/2010″ will not work. Use a Cell Reference as above or DATE(2010,10,12) or DATEVALUE(“2/10/2010″)

#### Other Useful Resources on Lookup Function.

A useful article from the knowledge team at the University of Wisconsin-Madison

A look at these functions from the Ohio State university