# Show a specific value in the cell when the two conditions are met

I am trying to prepare order form.

What should I do to achieve the final result that I describe below? I tried to use the IF function, but I think I am doing something very wrong. Please help. I hope that I can clarify my problem.

Here you can find my sample Google Sheet with a sketch of Order Form showing the situation. https://docs.google.com/spreadsheets/d/1sGpIl8PoPEcezU0NQpEn3FzWKxxeOe5q0O0JpM7jTX0/edit?usp=sharing

Description of the expected end result:

The end result is to be an automatic appearance of the price in the `cell` after the two variables selected. In the form, the variables are products and currency. The user chooses one of three currencies and then selects the product. After that, in the `cell` next to the product name, the price in the previously selected currency appears. There is space to enter 10 products in the form, so the function (or script) should refer to 10 different `cells`.

In `Sheet1` you can find the Order Form and you can choose the product and the currency.

In `Sheet2` you can find the products names and prices in three columns for three currencies. The product column is called `ProductName`. Columns with currencies have names in order: `CurrencyEur`, `CurrencyUsd` and `CurrencyGbp`.

• Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Dec 21, 2020 at 8:30

`=ArrayFormula({"Unit price";IF(B2:B="",,IFERROR(VLOOKUP(B2:B,Sheet2!A:D,HLOOKUP(E2,{Sheet2!A1:D1;SEQUENCE(1,4)},2,FALSE),FALSE),"NOT FOUND"))})`

This formula will create the header.

Under it (as marked by the semicolon), it will first check to see if any cell in B2:B is null/blank. If it is, then nothing happens.

If there is something in any cell from B2:B, the rest of the formula will kick in.

`IFERROR(...,"NOT FOUND")` will return "NOT FOUND" if there is any problem finding a match. This is only a precaution and should never happen as the sheet is currently set up.

`VLOOKUP(B2:B,Sheet2!A:D,...,FALSE)`

This will lookup any non-null value in B2:B within the second sheet and "stop at" the row of an exact match. The `HLOOKUP` (next) will decide which column value to return.

`HLOOKUP(E2,{Sheet2!A1:D1;SEQUENCE(1,4)},2,FALSE)`

The `HLOOKUP` decides which column value to return via the `VLOOKUP`. The `HLOOKUP` will look for the currency abbreviation within a virtual array made up of the headers from `Sheet2` placed above (semicolon) a `SEQUENCE` of numbers that is 1 row and 4 columns in size (filled, by default, with 1 2 3 4). The second row of this virtual array will be returned, which will wind up being the column number where the currency abbreviation is found.

If the formula does not work, try replacing every comma with a semi-colon, since your locale may require that formatting.

• Nice use of `HLOOKUP` Dec 21, 2020 at 5:14
• Wow Erik! Just one, big Wow! Thank you very much for this clear and great help. Dec 21, 2020 at 6:58
• Thanks, @marikamitsos. Dec 21, 2020 at 8:07
• Happy to help, @Ya Dzia. Would you take a moment to mark the answer as "Best Answer"? This will help others here to know that the issue has been fully resolved. Dec 21, 2020 at 8:07
• @ErikTyler One more question. Now everything works just perfect Prices appear where they should appear. But how make it possible to enter your own prices manually? Sometimes it can happen that the price is different from the one on the list. If I change the price manually, the function stops working and an error occurs. Please check: docs.google.com/spreadsheets/d/… Dec 22, 2020 at 7:18