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,884
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,884
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,884
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,081,852
Messages
5,361,694
Members
400,648
Latest member
mamamia93

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top