Thread: Lookup or Macro ? - Not sure which is best?

Hi,

In my work, we use spreadsheets which have the same content and repeat a lot.

The thing is that whilst the majority replicates, the content is not in the same position on each & every worksheet.

We have bills of quantites to price. The detail/description is provided in column A (and normally over approx 400 rows) and the pricing is normally covered in column D.

Each bill may have the similar detail/description in column A but it is NOT exact.

What I am trying to achieve, is to be able to use an already priced Bill as a template, then, use a Macro ? or Lookup? (or anything else that may handle the job) to identify the content in column A (in otherwords, match the detail/description), returning the price in column D, it having recognised that the content in say column A matches in the already priced bill and returns the correct price per item in column D.

I've attached a worksheet which will help explain better.

Thanks.

Hi,

As simple VLOOKUP() will work perfectly well. It would be preferable if all your item descriptions had some sort of code, but there is nothing to stop you using the item text as the subject of the VLOOKUP(). Of course this does mean that the item descriptions have to be spelled exactly the same, which is why I suggest having a code would be better.

However, assuming your Example B Bill is on Sheet2, (with the item appearing in A6, just enter

=VLOOKUP(A6,Sheet1!A:D,4,FALSE)

It will work just as well with the original Bill in a different workbook. The function would just need modifying to include the book name.

HTH