search worksheet for a value and return entire row to another worksheet

[TheSourceBookstore]

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?

[#2]

@TheSourceBookstore 

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.

[#3]

@mathetes 

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.

[#4]

Correction on XLOOKUP........
=XLOOKUP(A2,$C$2:$K$11,C2:K2)

[#5]

@TheSourceBookstore 

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.

[#6]

@TheSourceBookstore 

P.S. Where is "The Source Bookstore" located (if that's not too invasive to ask)... what sort of books do you carry? The few titles in your sample looked interesting.

[#7]

@mathetes 

The Source Bookstore is affiliated with Sherwood Baptist Church in Albany GA. We've been here 20 years. As you probably guessed, we're sell Christian books, movies, gifts, pastor/teacher resources, and church supplies. Our current website (above link) doesn't display all products.

I'm currently learning a new point-of-sale (POS) software that will give us greater flexibility. I'm trying to "clean-up" the inventory before the transfer. That's where you came in. Sheet 1 is barcodes from current inventory. Sheet 2 is what's in POS . I'm trying to remove the items we no longer carry.

Maybe I'll hear from you as a customer :)

[#8]

@TheSourceBookstore 

I hope the technical help I gave you was sufficient ... let me know if not.

I'm going to send you a private message here on the techcommunity site; you'll be able to access it via the little envelope icon up at the top right of your screen.

[#9]

@mathetes 

I hope to have a chance to look at it closely later today.

You can send to TheSource@SherwoodBaptist.net.  

[#10]

@mathetes 

Sorry it took so long to reply. I've attached an Excel spreadsheet showing sample data, XLOOKUP I tried (and failed), and comments describing what I need using cell references.

Hope the comments aren't too confusing. If you have any questions, please let me know.

Thanks again for all the help!

[#11]

@TheSourceBookstore 

This revision of what you posted now contains my comments in response to yours. Yours made sense, although as you'll see, I have approached it (as does XLOOKUP) from a slightly different perspective.

So now you can let me know if you have any questions.  I am only matching column A with column E, because that seems to be the only column with data comparable to that in A. XLOOKUP then can return the values from all the other columns in the rows where there is a match. I think this accomplishes what you want, but if not, let me know.

[#12]

@mathetes 

The whole purpose was to remove items no longer carried from the inventory database. I can run this for the entire database, filter out "No Match", then copy matches to csv file for importing into our new POS system.

This work for me! 

Thank you so much for your patience and of course the help. I enjoyed "meeting" you. I may be back later with more questions :)

Have a blessed day!