Excel, Apple. How do auto fill one cell with corresponding info based on entry of another cell?

I have three sheets.

• Sheet 1(Model Number)
• Sheet 2(Item Number)
• Sheet 3(Reference).

I want to be able to put a model number of an item in to a certain cell (J17) on sheet 1 and have it auto fill the corresponding item number in to a cell (B2) on sheet 2. I have the reference sheet set up with cell D3 as the item number and cell E3 as the item number.

I have used the following formula:

 =VLOOKUP('Phase 1'!J17, Reference!D3:E42, 2)

This is giving me an item but not the correct item number. Is there anyway to explain to me what I am doing wrong?

Yes there is a way to explain the results.

There seems almost no chance the data is sorted before doing the lookups. If it actually is sorted, it seems there is almost no chance the data is sorted by the column the lookup is performed in (D3:D42).

VLOOKUP() has a fourth parameter. You have only used the first three. Since the fourth parameter deals with sorted vs. not sorted data, it even more seems like the lookup data is not sorted.

So, the explanation first then solution: If the fourth parameter is TRUE or if it is not used (as in this case), the lookup seeks the lookup value until it finds either it, or a value it considers "greater than" the lookup value. At that moment it stops and gives you either the result desired (if it found the exact match) or the result immediately preceding (if it found a value "greater than" the lookup value).

You did not use the parameter, so that is what is happening. Now and then, you probably have gotten correct results (or would, if you did enough lookups). But chance would likely give you lots more wrong results.

And solution: USE the fourth parameter. Add FALSE to the function so it looks like:

=VLOOKUP('Phase 1'!J17, Reference!D3:E42, 2, FALSE)

When the fourth parameter is FALSE, VLOOKUP() looks ONLY for the exact value. If it finds it, it returns the appropriate result. If it cannot find the exact value, it returns an error.

This is what is needed in your situation.

Why would it be so? What use is there for this?

It is useful for lookups in which one wants data from a list of entries and knows an exact match is almost impossible. Consider a commission lookup. Perhaps a salesman selling $2,000 in a week gets a 5% commission. If he sells $5,000, he gets a 10% commission. More? An 18% commission. There is no real chance he'll sell exactly $2,000 or $5,000. Say he sells $3,208.77. The lookup passes by the $2,000 entry and finds the $5,000 entry. It stops and returns the entry immediately before $5,000 which is the $2,000 entry, or 10%. There would be a third entry, say $999,999, that would be ridiculously higher than any possible sales level. It's column 2 value would be the 18% for sales greater than $5,000.

This way, a lookup with a value between the ranges returns the right thing rather than failing if you don't provide a line for every possible penny of sales.

So use FALSE as the fourth parameter any time you wish to work on a general list of values like names or dates. Use TRUE if doing a lookup like the sales commission lookup (or tax table, or letter grades, etc.)