Hi,

I am putting together a very simple worksheet for some sales tracking
and I have hit a bit of a road block when I am trying to utilise the
Lookup and Index functions.

I want to be able to pick the type of sale in 1 Coolum from a drop down
list (have this list working ok). Depending on which option is chosen
(5 possible options), I want to populate a cell with the cost data
relating to that particular product. I have tried IF statements (which
are fine if I only had 1 option) and HLOOKUP - example below but I'm
defiantly missing something.


=INDEX($O$2:$P$7, MATCH("NVQ",$O$2:$O$7,), MATCH("Cost",$O$2:$P$2,))

Any help would be much appreciated!

Chris

Chris,

First of all, your formula will always produce the same result. You
would need some cell references in lieu of the text constants for it to
be variable.

Second, the cell ranges seem to indicate that row 1 (and possibly
column N:N - before O:O) are used for headers. Your data range consists
of only 2 columns, what are their headers?

Third, you probably need to add a 0 as 3rd argument to MATCH, to ensure
an exact match, otherwise you might get erroneous results. Could the
following formula be what you seek?

=INDEX($O$2:$P$7, MATCH("NVQ",$O$2:$O$7,0), MATCH("Cost",$O$1:$P$1,0))

Finally, you speak of a drop-down. Are you talking Data Validation or a
combo/list box that you have created from the Forms toolbar? If so, the
value returned in a cell is the relative position in the input range
and not the actual value. If your combo box is linked to cell A1 you
would need the expression:

INDEX(G1:G5,A1)

to get the actual value of the product, assuming that G1:G5 holds the
list to appear in the combo box.

Does this help?
Kostis Vezerides

ChrisHodds wrote:
Hi,

I am putting together a very simple worksheet for some sales tracking
and I have hit a bit of a road block when I am trying to utilise the
Lookup and Index functions.

I want to be able to pick the type of sale in 1 Coolum from a drop down
list (have this list working ok). Depending on which option is chosen
(5 possible options), I want to populate a cell with the cost data
relating to that particular product. I have tried IF statements (which
are fine if I only had 1 option) and HLOOKUP - example below but I'm
defiantly missing something.


=INDEX($O$2:$P$7, MATCH("NVQ",$O$2:$O$7,), MATCH("Cost",$O$2:$P$2,))

Any help would be much appreciated!

Chris