# Return value by lookup from multiple value

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

supplier offers

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

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:

• 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, 2022 at 3:58

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.

• Dear David, thank you for your reply! Unfortunately something is still missing from the formula Jun 19, 2022 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, 2022 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 Jun 20, 2022 at 0:30