Search code examples
google-sheetssearchvlookup

Search a column for specific text, Return value of cell within that row to another sheet

I currently manage a warehouse full of tools. Each tool has a specific ID# attached to it, along with a specific location in which it is stored within the warehouse.

To give you an idea of how my tools are being organized, here is a screen shot that I will explain: screenshot

The first 4 columns are just for organizational purposes. The 5th column is the full tool ID#. The next column is the description of the tool, TOOL ROOM is the main location, and the columns after that specify where in the TOOL ROOM the tool is specifically located.

I would like to create a simple spreadsheet that will search for tools that are located on this spreadsheet in the TOOL ROOM, and return the TOOL ID# to the new spreadsheet. I will then use that same formula to return the description of the tool in a new column on the new spreadsheet.

I researched using VLOOKUP and other formulas, but I can't seem to wrap my brain around specifically how to make this happen.

Any help would be greatly appreciated, thank you!

I have tried VLOOKUP, but I cannot wrap my brain around the specifics of what I am trying to accomplish how that translates to formulas.

I have also seen someone who had a similar issue who apparently got it to work using INDEX & MATCH, but I haven't had luck with that.

Solution

  • This is not totally achievable with VLOOKUP because VLOOKUP looks only to the right of the item being searched. Use XLOOKUP instead. (This also assumes that each tool has a unique name.)

    Here are some screenshots of sample data that approximates your data. Sheet1 is the tool database.

    Tool Database

    On Sheet2, I have set up a cell (A1) in the example with the Data Validation option that allows you to select a tool from the list of tools in B2:B on Sheet1.

    Data Validation

    Then, using XLOOKUP, I have put the formula =XLOOKUP($A$1,Sheet1!$B$2:$B,Sheet1!A2:A) in cell A4 on Sheet2. In this formula in A4, search_key is the tool selected from the dropdown in A1, lookup_range is the list of tools in B2:B on Sheet1, and result_range is the tool ID in A2:A on Sheet1.

    First XLOOKUP

    You can then populate any additional columns by using this formula, changing only the result-range as needed.

    Second XLOOKUP

    Note: If these are two separate spreadsheets, you can import the tool database to a separate tab of the spreadsheet where you are doing the search using IMPORTRANGE.

    Modified Answer Based on Comment

    If you have multiple tools of the same name, you could use the following formula in Sheet2!A4:

    =FILTER({Sheet1!A2:A,Sheet1!C2:C,Sheet1!D2:D,Sheet1!E2:E},Sheet1!B2:B=$A$1)

    This will return the whole range where the name in the tool column matches the value in Sheet2!A1.

    Filter Screenshot