0

We constantly receive offers from suppliers and requests from customers. The problem is that customers and suppliers usually have several different identifiers for the same product. Like this:

customer requests enter image description here

supplier offers enter image description here

In order see the matches between the offers and requests, I need to create a sheet with the unique products with the Product Name and Main ID we want to use. Next to the Main IDs, I need to enter all the alternative IDs that I met in offers and requests. Something like this:

product_data enter image description here

My question is what is the best formula to make google sheet look for the IDs in offers/requests from column C to column I of product_data sheet (from ALT ID 1 to ALT ID 7 )and based on that, return the MAIN ID of the product in offers/request? like this: enter image description here

1
  • 1
    Welcome to Web Applications Stack Exchange. You might find this easy to handle with vlookup() if you changed your "wide" product_data table to a "tall" table where the alt IDs are all in column A and the relevant main ID is in column B, repeated on multiple rows as necessary. Jun 18 at 3:58

1 Answer 1

0

Try this:

=QUERY(
    'ID-LookUp'!$A$2:$I$3,
    "SELECT B WHERE " &
    REGEXREPLACE(
        CONCATENATE(
            ArrayFormula(
                SPLIT(
                    "B,C,D,E,F,G,H,I",
                    ","
                ) & " = " &
                CHAR(
                    34
                ) & A2 &
                CHAR(
                    34
                ) & " OR "
            )
        ),
        "OR $",
        ""
    ),
    0
)

Explanation

Alright the REGEXREPLACE thing first. We are basically first generating the WHERE clause for the QUERY function there. we take the array which just has B through I then append to each = "ID" OR. The ID part we get from the A column. We then use REGEXREPLACE to remove the redundant "OR" at the end.

We move now to the main QUERY function. We select the data only (A2:I3 not A1:I3). This is to make sure that the header doesn't show up. Next we select B since it contains the Main ID we want to return. We then append the string and done.

3
  • Dear David, thank you for your reply! Unfortunately something is still missing from the formula Jun 19 at 17:12
  • Dear David, thank you for your reply! Unfortunately something is still missing from the formula, the following #VALUE error occurs: "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered "<ID "WHEREB" at line 1, column 10. Was expecting one of: <EOF> "where" ... "group" ... "pivot" ... "order" ... "skipping" ... "limit" ... "offset" ... "label"... "format"... "options" "," ... "*" ... "+" ... "-" ... "/" ... "%" ... "+" ... "-" ... Can you please help me with what else to add to the query? Jun 19 at 17:24
  • the error is saying that there is WHEREB somewhere in the function, though that seems strange since there is a space in the string. If you are able to provide a copy of your gsheet, that would help immensely. @TamásHéjja
    – David Tan
    Jun 20 at 0:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.