0

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.

Community
  • 1
  • 1
SpidrJeru
  • 119
  • 3
  • 13
  • Maybe an example of your sheets would help. – Fernando Aires Oct 07 '14 at 04:38
  • 2
    I just added a screenshot of my spreadsheet. The price list on paper is described in the post. Thanks! – SpidrJeru Oct 07 '14 at 04:45
  • Once you have your new table of references & prices, a simple [VLOOKUP](https://forum.openoffice.org/en/forum/viewtopic.php?t=46746) in either LibreOffice or [Excel](https://support.office.com/en-US/Article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-US&rs=en-US&ad=US) will cross-match the existing table against the reference/price table to retrieve the current price. The separate reference/price table may be an easier way to maintain current pricing in the future. –  Oct 07 '14 at 04:46
  • do you think it would work for spreadsheets? – SpidrJeru Oct 07 '14 at 05:04
  • @pnuts thanks for the OCR suggestion. After looking into it a bit, I think I'll go this way. Only thing now is for me to figure out how :) – SpidrJeru Oct 07 '14 at 06:24

1 Answers1

2

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.

Fernando Aires
  • 532
  • 2
  • 7
  • great, it sounds like what I want! Could you please guide me a little bit though your answer? For example where to put the formula, and which column range should I put in the formula: the one from the inventory, or the newly created price list? Sorry for my ignorance on the matter. – SpidrJeru Oct 07 '14 at 05:01
  • 1
    You put the formula in the cell you want to get the price result, and the range is the newly created price list (the two columns). – Fernando Aires Oct 07 '14 at 05:03
  • 1
    (And, please, don't feel sorry for not knowing something. That's what we all are: not knowing bunch of hormones, willing to fill the gaps... ;) ) – Fernando Aires Oct 07 '14 at 05:04
  • ok, I tried and I get an `err:502`. My function is `=VLOOKUP(Sheet7.A3:A6, Sheet7.B3:B6, 2, 0)`. As you can see I entered the pricelist in sheet7 and I tried on a bunch of references that were the same to try it out. – SpidrJeru Oct 07 '14 at 05:13
  • 1
    ok it works! My formula was wrong. It has to be `=VLOOKUP(A584, Sheet7.A3:B6, 2, 0)`. Thank you! – SpidrJeru Oct 07 '14 at 05:24
  • 1
    @pnuts indeed, thanks for pointing it out. I figured it out eventually though. – SpidrJeru Oct 07 '14 at 08:52