Fill cell with data depending on a different cell

[241ec994-3fac-103c-9]

Hello there! This is my first post on this forum, so I apologize in advance if I’m unnintentionally breaking any rules. I am relatively new to spreadsheet software, but I have been searching for days in this and other places for a solution and can’t find exactly what I’m looking for.

I’m working on a document for budgeting. I have a catalogue of products on column A, and their corresponding prices on column B. To calculate the budget total, I’m writting the product names on column D and I need calc to autofill the corresponding product price on column E. Here’s a picture of this example (the actual doc is more complex and the catalog much much longer):

Since the catalog is pretty long, I can’t use the =IF(D3=“Table”;“10”;"") type of formulas I would normaly use, so I’m out of my depth here. That being said, the catalog doesn’t have to be on the same sheet as the budget, so if there’s another way to accomplish what I want, that works too. Thanks a lot for reading this!

[keme1]

VLOOKUP() is your friend. (Click the function name to open documentation page which explains the use of the function.)

Make sure you enter the fourth (optional) parameter (SortedRangeLookup) as zero or FALSE. This ensures two important things:

• You get correct searching with unsorted names in the first column.
  Lookups inspreadsheets will by default assume that the “key column” is sorted.
• An error is shown when you don’t get an exact match.
  E.g. to catch typos. You don’t want the search to resort to “nearest match” for this kind of lookup.

For the lookup table you need to use absolute addressing. A named range is often useful for this kind of situation.

You may also want to allow for empty rows. This can be solved by some =IFERROR() or =IF(ISBLANK()) construct.

[241ec994-3fac-103c-9]

Thanks a lot! That seems to be the solution, or at least it works on the example doc, I’ll try it on the actual one when I get my hands on it. I don’t know how constructs work (yet) but I’ll look into that as well.

Is there a way to avoid #N/D showing when the search criteria cell is empty? Like it does here:

[erAck]

@241ec994-3fac-103c-9

avoid #N/D showing when the search criteria cell is empty

Assuming #N/D in your localization is #N/A: by using IFNA() and stating the desired error case result, here literal empty string:

=IFNA(your_formula_expression;"")