How to return a specific serial number when a user has multiple assigned in their name.

[darkdelusions]

I currently have 2 sheets of serial numbers sheet 1 has all of the new serial numbers on it and sheet 2 has both old and new serial numbers on. I am currently trying to bring all of the old serial numbers into the sheet 1 but due to the nature of index match and vlookup I am only able to pull in "new serial number".



Sheet 1:

Sheet 2:

Can anyone provide me some guidance on how to get the serial number from sheet 2 if its not in the serial column on sheet 1.

 

[Scott Huish]

In Sheet2, would the old serial # always follow the new one, in the next row, if it exists?
So in your example, the new serial # is ABCDE and the old one is FGHIJ? Is this correct?

 

[Scott Huish]

If so, try this.

Formula in D2:
=VLOOKUP(B2,Sheet2!$B:$D,3,0)

Formula in E2:
=IF(COUNTIF(Sheet2!B:B,Sheet1!B2)=2,INDEX(Sheet2!D:D,MATCH(B2,Sheet2!B:B,0)+1),"")

Copy down.

Excel 2010
	A	B	C	D	E
1	Name	Email	Phone Number	Serial	Old Serial
2	Bob Smith	Bsmith@Bob.Com	555-555-5555	ABCDE	FGHIJ
3	Steve Dave	sdave@bob.com	555-555-5555	VXYZ
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,Sheet2!$A:$C,2,0)
B3	=VLOOKUP($A3,Sheet2!$A:$C,2,0)
C2	=VLOOKUP($A2,Sheet2!$A:$C,3,0)
C3	=VLOOKUP($A3,Sheet2!$A:$C,3,0)
D2	=VLOOKUP(B2,Sheet2!$B:$D,3,0)
D3	=VLOOKUP(B3,Sheet2!$B:$D,3,0)
E2	=IF(COUNTIF(Sheet2!B:B,Sheet1!B2)=2,INDEX(Sheet2!D:D,MATCH(B2,Sheet2!B:B,0)+1),"")
E3	=IF(COUNTIF(Sheet2!B:B,Sheet1!B3)=2,INDEX(Sheet2!D:D,MATCH(B3,Sheet2!B:B,0)+1),"")

 

[darkdelusions]

the serial numbers reports is pulled from one of my tools so row the data is in can be random.

 

[Scott Huish]

Then what indicates if it is a new serial # or an old serial #?

 

[darkdelusions]

Then what indicates if it is a new serial # or an old serial #?

The new serial is provided to me from another tool and statically stored in Column C on Sheet 1 and then on sheet 2 i have about 12000 users and serial numbers and I want to get the old serial. In theory should be something like this but doesn't seem to work and I have tried both table names and Column and row numbers

=INDEX(Table1,MATCH(1,(Table1[Email]=[Email])*([Serial]<>Table1[Serial]),0),4)

 

[darkdelusions]

This can me marked as resolved, Once I went back to it and tinkered with the formula I posted above it started working.

Thank your for trying to help Scott