CODEWITHSUNDEEP
excelexcel-formula
Volearix
Volearix

Reputation: 1593

Formula to find data in three different worksheets

I have a data set with 3 worksheets similar to the follow:

AS400 Ref enter image description here

Model Ref enter image description here

Registration Ref enter image description here

There are three different lists all containing roughly the same data; AS400/Model/Registrations. What I want to do is extract a unique set of zip codes from all 3 lists and put them on a master worksheet (the easy part), then what I need is something that looks at every zip code on the master page, then looks at every other page and returns the matching Branch column if it's found. Finally, on the master page I'd like to be able to determine if all 3 match, or if there is an 'odd ball' within them. This is an example of what I'm looking for:

enter image description here

Any suggestions of a formula I could use to return the part finding all of the zip codes. I'm at a loss and not familiar enough with excel to know where to start.

Upvotes: 0

Views: 171

Answers (3)

Youbaraj Sharma
Youbaraj Sharma

Reputation: 1295

Assuming you have three sheets as you have mentioned AS400 Ref Model Ref Registration ref and the zip codes being consolidated in the sheet named main. In the main sheet on the registrationbranch column (which in my reference is column b in main sheet) use the Vlookup formula

=IF(ISERROR(VLOOKUP($A2,'AS400 Ref'!$A$2:$B$16,2,0)),"",VLOOKUP($A2,'AS400 Ref'!$A$2:$B$16,2,0))

Similarly in the AS400BRANCH and ALLMATCH columns (which in my example is Column C and Colunm D use these formula respectively

=IF(ISERROR(VLOOKUP($A2,'Model Ref'!$A$2:$B$16,2,0)),"",VLOOKUP($A2,'Model Ref'!$A$2:$B$16,2,0))

=IF(ISERROR(VLOOKUP($A2,'Registration ref'!$A$2:$B$16,2,0)),"",VLOOKUP($A2,'Registration ref'!$A$2:$B$16,2,0))

Note that you need to change the reference form A2 to B16 based on your data in different sheets.

Here is the structure of the main sheet for your refrenceenter image description here

Upvotes: 0

pnuts
pnuts

Reputation: 59475

For a large data set I suggest INDEX and MATCH (and only only column at a time before replacing formulae with values):

SO20664665 example

The example is the formula for 'Historical'.

Upvotes: 1

Jerry
Jerry

Reputation: 71548

Okay, first of all, the zip should be of the same format. If they're text in the master page, they should be text in the relevant sheets.

Next, you can use VLOOKUP to retrieve the Branch ID from the relevant sheets, and you'll need only 3 variations of this formula; one for each column.

I will be assuming that all your tables have the header zip in cell A1; so change the references if required, and that the sheets are named AS400 Ref, Model Ref and Registration Ref

In the master page, cell B2 (that's the column ModelBranch and row for 17070), use the formula:

=VLOOKUP($A2, 'Model Ref'!A:B, 2, 0)

In C2 and D2, use the formulae:

=VLOOKUP($A2, 'Registration Ref'!A:B, 2, 0)

=VLOOKUP($A2, 'AS400 Ref'!A:B, 2, 0)

You can then drag the formulae down as far down as required. This should retrieve the required Branch ID. If there is a possibility that the zip is not found, then put the whole VLOOKUP into an IFERROR. For instance, the first would become:

=IFERROR(VLOOKUP($A2, 'Model Ref'!A:B, 2, 0), 0)

VLOOKUP takes the first parameter ($A2 in this instance), looks it up in the first column of the table array and then looks into the second column (hence why the 3rd parameter is 2) for the value to retrieve, which is from column B (if the value was to be obtained from column C, then you'd use 'Model Ref'!A:C, 3).

To get the last part now, about the match or not, you can use this:

=IF(AND(B2=C2,C2=D2), "Yes", "No")

This checks if B2 = C2 = D2, in which case returns Yes, otherwise, No.

Upvotes: 1

Related Questions