VLOOKUP

Ok, here goes:

Say you have the following data on sheet1 (cells A1-C3):

UPC AMOUNT SOMETHINGELSE
123 2.00 X
234 3.00 Y
456 4.00 Z

And the following info on sheet2 (same cells as sheet1):

UPC BUYER SELLER
234 Jones Bill
332 Smith Mary
123 Fuentes John

Now, if you wanted to return the buyer to sheet1 (say column D, since we are currently using A through C), you could do a VLOOKUP. On sheet1, in cell D2 (say row 1 holds the column names - UPC, AMOUNT, SOMETHINGELSE), you would type the following formula:

=VLOOKUP(A2, sheet2!$A$2:$C$3, 2, FALSE)

This would bring the buyer's name into cell D2.

To explain each part, the A2 is the value we are trying to match (the UPC in this case); the sheet2!$A$2:$C$3 is the area we are searching for a match in; the 2 is the number of the column from the area we are searching in (sheet2!$A$2:$C$3) to return; and FALSE tells VLOOKUP to return only an EXACT match. What VLOOKUP does is it will search DOWN (vertically) the first column of the area you have specified to look in (again, sheet2!$A$2:$C$3) for a match for the value you specified (in our case A2 of sheet1), and if it finds one, it will return the column number you specified in your VLOOKUP formula (in our case, 2).

So with our formula in D2, VLOOKUP will go to sheet2, in the area we specified. Since column A is the left column of the area we specified, it will start down that column starting at A2. The first value it sees is 234. That's not a match, so it moves down one row, and sees 332. That's not a match either, so it moves down and finds 123. That is a match (of A2 in sheet1), so it will return the value in column 2 of the area we specified -- IN THE ROW IT HAS CURRENTLY FOUND. So "Fuentes" would be returned to D2 on sheet1. Now, if we pulled the formula down from D2 to D4, D3 would have "Jones" as it's return value, and D4 would have "#N/A" as it's return value. The #N/A says that it did not find a match.

Now, when using VLOOKUP you almost always have a value greater than or equal to 2 as your return column, because if you put 1, then it would just return the value itself, which is usually useless. However, in your case, if you are just wanting to know if there is a match or not, you could use 1 as your column return. A formula you could use might be something like this:

=IF(ISNA(VLOOKUP(A2, sheet2!$A$2:$C$3, 2, FALSE)),"No Match","MATCH!")

The ISNA does what it seems like it would: it returns true if your formula returns #N/A, and false otherwise -- in other words, if VLOOKUP returns a match, ISNA will be false.

VLOOKUP is hard to grasp at first, but once you get it you will love it. I hope I have done a decent job of explaining it in words. Feel free to email me a sample workbook and I will show you how I would do the formulas.

Russell

Lisa,

Select all the cells with UPC codes on "page 1" and name the selection UPCcodes via the Name Box or via the option Insert|Name|Define.

On sheet #2 (page 2), activate the cell next to the first UPC code (lets say this cell is B1) and type in B1

=IF(ISNUMBER(MATCH(A1,UPCcodes,0),"","Different")

Copy down this formula as far as needed. You'll see UPC codes that do not exist on Page 1 marked as different.

Repeat the same procedure on Page 2.

Note. MATCH is just like VLOOKUP one of the so-called lookup functions in Excel.

Aladin

Thought I'd have a go to

Vlookups work much the same as finding a telephone number i.e. look down the list of names and return a number one col to the right. =VLOOKUP(PhNum,NameCol:PhNumCol,2,false) and will return an error if the name is not there. Because vlookup will return an error (#N/A) if there is no match the formula will get awfully complicated as the function will have to look in two pages i.e. If the name is not on this page then is it on the next page, if the name is not on either page then the name is not here, return an error (#N/A).

An alternative would be to count the number of times the UPC number appears in either of the lists if the function returns then 0 then it's not there.

=if(countif(A1,Sheet2!'$A$1:$A$100)+countif(A1,Sheet3!'$A$1:$A$100)<>0,"Match Found","No Match")

I don't have Excel at home to check the syntax so use the paste function wizard (fx on the toolbar) to write the formula or experiment with components of it.

Another alternative is to use the conditional sum wizard in the Data or tools menu. You might need to enable the addin in Tools AddIns menu. The wizard creates an array formula, array formula can be tricky though.

Thanks for the plain English!

I always had trouble understanding the Excel Help on this one too!