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

darkdelusions

New Member
Joined
Nov 11, 2019
Messages
4
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
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.


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Name</td><td style=";">Email</td><td style=";">Phone Number</td><td style=";">Serial</td><td style=";">Old Serial</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bob Smith</td><td style=";">Bsmith@Bob.Com</td><td style=";">555-555-5555</td><td style=";">ABCDE</td><td style=";">FGHIJ</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Steve Dave</td><td style=";">sdave@bob.com</td><td style=";">555-555-5555</td><td style=";">VXYZ</td><td style=";"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A2,Sheet2!$A:$C,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A2,Sheet2!$A:$C,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B2,Sheet2!$B:$D,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet2!B:B,Sheet1!B2</font>)=2,INDEX(<font color="Red">Sheet2!D:D,MATCH(<font color="Green">B2,Sheet2!B:B,0</font>)+1</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A3,Sheet2!$A:$C,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A3,Sheet2!$A:$C,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">B3,Sheet2!$B:$D,3,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=IF(<font color="Blue">COUNTIF(<font color="Red">Sheet2!B:B,Sheet1!B3</font>)=2,INDEX(<font color="Red">Sheet2!D:D,MATCH(<font color="Green">B3,Sheet2!B:B,0</font>)+1</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,835
Then what indicates if it is a new serial # or an old serial #?
 

darkdelusions

New Member
Joined
Nov 11, 2019
Messages
4
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

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

darkdelusions

New Member
Joined
Nov 11, 2019
Messages
4
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
 

Forum statistics

Threads
1,077,919
Messages
5,337,183
Members
399,131
Latest member
Vinnyjuice

Some videos you may like

This Week's Hot Topics

Top