Vlookup with multiple matches

shre0047

Board Regular
Joined
Feb 3, 2017
Messages
52
Office Version
  1. 365
Platform
  1. Windows
I'm attempting to create a vlookup, but the lookup value may have more than one entry. Is there a formula I can use which to

IF "lookup value" was referenced in a previous cell columns
THEN return value for 2nd,3rd,4th, etc.
ELSE return value for 1st.

Sheet1
CheckStatus (returned via vlookup)
2345Bad
1234Good
1234Bad
1234Good

Data in Sheet2
Column1Column2
1234Good
2345Bad
1234Bad
3456Good
1234Good
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Try this one in B1 of Sheet1, then fill down. The formula allows for data down to row 100 in sheet 2. Using full columns with this is not a good idea.

=IFERROR(INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(Sheet2!A$1:A$100)/(Sheet2!A$1:A$100=Sheet1!A1),COUNTIFS(A$1:A1,A1))),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
Another option
+Fluff New.xlsm
ABCDEF
1Column1Column2CheckStatus
21234Good2345Bad
32345Bad1234Good
41234Bad1234Bad
53456Good1234Good
61234Good
7
Data
Cell Formulas
RangeFormula
F2:F5F2=INDEX(FILTER($B$2:$B$6,$A$2:$A$6=E2),COUNTIFS(E$2:E2,E2))
 

shre0047

Board Regular
Joined
Feb 3, 2017
Messages
52
Office Version
  1. 365
Platform
  1. Windows
The data in Sheet2 will have 300-400 rows. Will the formula create any performance issues due to the size of the dataset?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Both formulae should be fine with that amount of data.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,934
Office Version
  1. 365
Platform
  1. Windows
Neither should be too bad with a data set that small, you could set the ranges to 1000 rows without issues.

An entire column is over 1 million rows, to put it into perspective, if you use an entire column with 10 thousand rows then 99% of the processing effort is being wasted.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,934
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top