Reconciling Contents From Multiple Sheets Using VLOOKUP

Reconciling Contents From Multiple Sheets Using VLOOKUP

When using VLOOKUP in Excel, sometimes you may end up with the ugly #N/A error. This happens when your formula can not find the lookup value on the first Lookup sheet and as a result, you had to look up the value on another sheet.

In this tutorial, I will show you a way to use IFERROR with VLOOKUP to reconcile values from multiple Worksheets.

Using the combination of IFERROR with VLOOKUP gives you the alternative of getting rid of the #N/A error and searching for the Lookup value on Multiple Worksheets.

Before getting into details on using this combination, let’s first quickly go through the IFERROR function and see how it works.

IFERROR:

With the IFERROR function, you can specify what should happen in case a formula or a cell reference returns an error.

Here is the syntax of the IFERROR function.

=IFERROR (value, value_if_error)

value – this is the argument that is checked for the error. In most cases, it is either a formula or a cell reference. When using VLOOKUP with IFERROR, the VLOOKUP formula would be this argument.

Suppose you have a dataset as shown below:

Reconciling Contents From Multiple Sheets Using VLOOKUP
Reconciling Contents From Multiple Sheets Using VLOOKUP
Reconciling Contents From Multiple Sheets Using VLOOKUP
Reconciling Contents From Multiple Sheets Using VLOOKUP

The first two Worksheets has the scores of the Students in CLASS 2B and CLASS 2C while the third Worksheet contains the scores for students in both Classes. We want to reconcile the scores of all students on the third worksheet from both CLASS2B and CLASS2C Worksheets.

The formula =VLOOKUP ($A2,’CLASS 2B’! $A$1: $B$8,2,0) shows the reason why we have the above error on the ALL SCORES Worksheet.

The Error was because the formula only considered Worksheet CLASS2B as the only data for the Lookup value disregarding Worksheet CLASS2C.

To fix this we will use the NESTED VLOOKUP and IFERROR Functions so while the next VLOOKUP takes care of the other dataset the IFERROR Function gets rid of the #N/A Error as shown below.

Here is the nested IFERROR formula I can use to look for the value:

IFERROR (VLOOKUP ($A2,’CLASS 2B’! $A$1:$B$8,2,0),VLOOKUP($A2,’CLASS 2C’!$A$1:$B$8,2,0))

Hope you found this helpful!

Do not forget to check out our training courses: https://www.urbizedge.com.

Leave a Reply

Business Data Analysis
close slider
[]
1 Step 1

Interested in our Business Data Analysis Training?

keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right