Search code examples
libreoffice-calc

Merge two spreadsheets by matching one column

I have an inventory list of items without prices. These items have a reference number in one of the column.

Data sample

The prices for these items are on paper price lists (1 column with reference, 1 column with price).

I want to match each items with its price.

Right now I am doing a search for each reference, then entering the corresponding price, but it is inefficient.

Is there a way to enter the prices manually in another spreadsheet in bulk (one column with references, one column with prices) and then applying some logic to match each duplicate reference with its price?

Maybe there is an even better solution that I didn't think of?

Note: I am using LibreOffice, but I could use Excel if absolutely necessary.

Solution

  • VLOOKUP function search for the first ocurrence of an element in the first row of a matrix, and return the value of one of the columns (defined by parameter).

    If I understood correctly your explanation, you can put the PriceList in a sheet, and then:

    =VLOOKUP(<reference_number_cell>, <price_list_interval>, 2, FALSE)

    <price_list_interval> represents both columns of the newly created price list (Reference+Price).

    This will get the reference number from the whole price list, and get the column 2 (the price) from it. Of course, you need to lock the references for the price list.

    VLOOKUP function can interpolate from intervals, if your numbers are ordered and he can't find the exact match. Since you're dealing with reference numbers, you shouldn't do it - and that's what that FALSE is for.

    Hope that helps.