Forum Discussion
search worksheet for a value and return entire row to another worksheet
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.