vlookup
The standard VLOOKUP formula that the gurus on these forums seem to
use, and the one you will find in many Help sources, is:
In C5 enter =VLOOKUP(C4,Product!$A$2:$C$13,2,FALSE)
In C6 enter =VLOOKUP(C4,Product!$A$2:$C$13,3,FALSE)
I don't like this approach because it leaves you vulnerable to column
insertions in the product Table, because it uses absolute values of 2
and 3 for the column index number. If I was forced to use VLOOKUP I
would use the following formulas in C5 and C6:
C5:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!B:B)-COLUMN(Product!A:A)+1,FALSE)
C6:
=VLOOKUP(C4,Product!$A$2:$C$13,COLUMN(Product!C:C)-COLUMN(Product!A:A)+1,FALSE)
These formulas look ungainly, but they do provide the same protection
against column insertions in the product table that is provided by the
shorter and, arguably, simpler INDEX/MATCH formulas.
If you were doing a class project and you submitted the INDEX/MATCH
solution, I would give you extra credit!
HTH
Declan O'R
|