[Solved] Help Using Index Match

[Ether42]

I have a taken on a new job and would like to make my life easier.

I am normally a Mac guy but this new job uses Open Office so I am struggling a little.

I have a Master list of this stores inventory/products including our SKU, Distributor, Name and UPC.

I also have a handheld UPC scanner that outputs a CSV file with the scanned UPC and a quantity count.

Ideally I would like to take the scanner, scan barcodes and get my CSV file.

Open the CSV and copy the column of the UPCs in to my master list.

Then using Index Match have it look at the UPCs find the distributor and return that value for me.

PIC1.gif (5.8 KiB) Viewed 11043 times

Using the image: Paste data in to column E compare and find the same data in column C, when a match is found return the Data in column D.

I have done something similar in Apple's Numbers but it has been a while.

BONUS QUESTION: How do I stop Open Calc from removing leading "0" in a number.

I looked at cell formatting but didn't find anything.

Example: It turns 04965802 in to 4965802

[FJCC]

Instead of INDEX and MATCH, use VLOOKUP(). In F2 the formula would be

Code: Select all

=VLOOKUP(E2;$C$2:$D$100;2;0)

With INDEX and MATCH it would be

Code: Select all

INDEX($D$2:$D$100;MATCH(E2;$C2:$C$100;0))

To prevent Calc from removing leading zeros, read the column in as Text. During the import process there should be a screen where you can define the column types. That is, if you start with the menu Insert -> Sheet From File.

[Ether42]

I was under the impression Index Match was preferred over VLOOKUP, is this not the case?

[FJCC]

I have never heard of a preference for INDEX/MATCH over VLOOKUP. Is there a reason associated with the claim?

[Ether42]

Your rock!

That worked a treat!

Last question. When I drag the formula down the list it shift's the formula down a cell each time so it isn't Indexing the whole column.

How do I fill down but keep the formula searching the whole column.

PIC2.gif (12.87 KiB) Viewed 11029 times

Side Note: Inserting columns breaks VLOOKUP I am pretty sure.

[FJCC]

I missed a $ in the formula. Try

Code: Select all

INDEX($D$2:$D$100;MATCH(E2;$C$2:$C$100;0))

The $ symbols keep the references fixed. Notice when you drag it down now that everything stays fixed except the reference to E2, which shifts down in each row.

[Ether42]

thought it was the $

You rock!

Thanks a million!