How to do a VLookup based on a field where there isn't a match

[Jaimee001]

Hi All!
I've attached an example work sheet. There are 2 tabs and in column b in each tab there is a number.
I need to append the number from tab2 to to tab1 IF it doesn't match the number in tab1. I thought I could use a VLoookup up but not sure how to say 'if not equal to'
Then (to make it more difficult) tab2 may have more than 1 number per account that doesn't match the number in tab1....I'm not sure if I need to do a 2nd formula or if there is a way to do it all at once.
I appreaciate all of your time and help.
Regards,
Jaimee

 

[Luke M]

In Sheet "tab1", cell H2, confirm this formula using Ctrl+Shift+Enter, not just Enter
=IFERROR(INDEX('tab2'!$B:$B,SMALL(IF(('tab2'!$A$2:$A$6=$A3)*('tab2'!$B$2:$B$6<>B3),ROW('tab2'!$B$2:$B$6)),COUNTIF(A$2:A3,A3))),"")

Formula will then be an array. You can then drag the formula down as needed.

 

[Jaimee001]

Thank you Luke, I'm almost there!
To make it work I had to make a small change to the formula. in the section: SMALL(IF(('tab2'!$A$2:$A$6=$A3)I changed to $A$2:$A$6=$A2. That works.
I also uploaded a revised report with the formula included. ROW 2 should be blank as the number is the same in both tabs. Not sure what I need to do
Also in report I'm using this on tab 1 has 7300 rows andtab 2 has 7500 rows. I think I have to change the countif part of the formula but not sure what I need to do.

 

[Luke M]

Sorry about that Jaimee, I must have been looking at the wrong wrong. You were correct, the COUNTIF needed to be changed. Formula would be:
=IFERROR(INDEX('tab2'!$B:$B,SMALL(IF(('tab2'!$A$2:$A$6=$A2)*('tab2'!$B$2:$B$6<>B2),ROW('tab2'!$B$2:$B$6)),COUNTIF(A$2:A2,A2))),"")

Just as a heads up, with that many rows, this might take awhile to do all the lookups.

 

[Jaimee001]

Thank you so much Luke, it works!!!!! YOU ARE AWESOME