Forum Discussion
search worksheet for a value and return entire row to another worksheet
I have a list of barcodes in a worksheet (sheet 1). I have another worksheet (sheet 2) with multiple columns including barcodes. Ultimately, I need to compare both worksheets based on the sheet 1 barcode and keep all sheet 2 values with matching barcodes and disregard those that don't match.
From what I could tell by looking at Excel functions, the DGET is similar to what I need. The only problem is it only returns 1 value. Sheet 2 has 12 columns I need to keep with the barcode.
Any suggestions for someone new to Excel?
11 Replies
It's interesting that you turned up DGET. One rarely sees references to that family of D____ database functions. They're powerful but quite infrequently used.
For your purposes, I'd first look at VLOOKUP. You describe yourself as a beginner (well, "new to Excel")...VLOOKUP has a long history and is sometimes described as the second or third most popular function after SUM. https://exceljet.net/excel-functions/excel-vlookup-function
Other folks use INDEX and MATCH together. https://exceljet.net/formula/basic-index-match-exact
And a newer function called XLOOKUP combines the best of all the foregoing. https://exceljet.net/excel-functions/excel-xlookup-function
If you can post a copy of your workbook (without any confidential information), I or somebody else here could give a more specific tailored suggestion. But if you like figuring out these things on your own--as digging up DGET suggests might be the case--maybe those references I've given can point you in the right direction.
Thanks for the help and which direction to look. I thought it would be better to try the newer XLOOKUP. I've been playing with the function and do have a few questions.
I seem to be having a problem with the Return Array argument. Here's what I was using:
=XLOOKUP(A2,$C$2:$K$11,M2) Questions:
1. When I get to the point where I use with the entire database, 2800 rows, and barcodes, 1200 rows, I was planning on putting a function on each each barcode's row. Would this work?
2. Do the lookup, database, and results have to in the same worksheet?
I've attached an abbreviated version of the data. The items in red should be the only match.
Thanks again.
I'm attaching an example using XLOOKUP on a second sheet to access your sample database. This single formula was copied across and down. The absolute and relative references allowed the formula to change to get each column from your original...you may not want that, but this shows how that could work.
=XLOOKUP($B3,sample!$A$2:$A$11,sample!C$2:C$11,"NotFound",0)
Now to your questions:
1. When I get to the point where I use with the entire database, 2800 rows, and barcodes, 1200 rows, I was planning on putting a function on each each barcode's row. Would this work?
My example shows that it does work. And if the formula is correctly constructed, you can just copy a single formula to every row below.
2. Do the lookup, database, and results have to in the same worksheet?
Again, my example illustrates that they don't. They can be. But they don't have to be. I have LOOKUP formulas that retrieve data from totally separate workbooks, in fact. That can get tricky, however, in that you don't want to be moving the source workbook around to different directories.... But the simple answer to your question is that you can readily have the lookup in a different worksheet than the database. The lookup (whether VLOOKUP, HLOOKUP or XLOOKUP) delivers the result in the cell where the formula appears, by the way, so "result" by definition is where the formula is.
By the way, nothing was in red in the spreadsheet, so I was unable to select those. If that's a block to understanding what I've done, please come back and describe the column by heading. Or attach it as an excel file rather than CSV.
