CODEWITHSUNDEEP
google-sheetsmatchgoogle-sheets-formula
Fledgling Sysadmin
Fledgling Sysadmin

Reputation: 43

Google Sheets Filter + Match? Not sure

I have two tabs in a sheet, that have different ranges. One is preset data, the other is an import from a program we use. I'm trying to figure out a way, if possible, to pull specific data from sheet 1, and match it with values in sheet two, that aren't sorted the same.

Example sheet https://docs.google.com/spreadsheets/d/1OsSWQ_48VrcTU3pXGeJ_1syluKPVeVRune39UA9I3x4/edit?usp=sharing

I'm trying the formula =sort(filter(Sheet1!B1:B,match(B1:B, Sheet1!B1:B ,0)),2,TRUE), but it's just putting the results in order. If I replace the column # on the sort with 1, it sorts it out of order but it doesn't match column B, like I need it to.

The documents i'm working with are 2000+ rows each, I'd rather not manually do this if at all possible.

Please assist? Either way, thank you for reading.

Upvotes: 4

Views: 6479

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

Try:

=arrayformula(iferror(vlookup(MATCH( B1:B,Sheet1!$B$1:B,0),{(ROW(Sheet1!A1:A)),Sheet1!A1:A},2,0)))

Explanation

  • step#1 =arrayformula(MATCH( B1:B,Sheet1!$B$1:B,0)) will give the row where each value column B will be found in Sheet1!column B
  • step #2 =arrayformula({ROW(Sheet1!A1:A),Sheet1!A1:A}) will build a virtual matrix whith in column#1 the row and in column#2 the value of Sheet1!column A
  • finally: join the two formulas as vlookup(___step#1____,____step#2______)

Upvotes: 4

Related Questions