Some excel help needed please

Hi,

I have been racking my brain all day using the help function in excel to try to create a formula but have failed miserably.

I have 3 columns ABC
Colum A has an item name, Column B has a cost and Column C has the currency.

In column F i have created a drop down list from the data in column A.
What i want is a formula in column G that when i choose something from the drop down list in F it returns the cost from column B associated with that item and the same to be done in column H but to return the currency.

Any help please before the PC end up the other side of the office.

Thanks

Assuming you have A1 as the first item name and C20 as the last currency, with F1 being the drop-down list cell:

=INDEX(A1:C20,MATCH(F1,A1:A20,0),3)

Thank you Camie, learnt another new thing.

Could also be done with the formula

=VLOOKUP($F$1,$A$1:$C$20,2,FALSE) for G1
and
=VLOOKUP($F$1,$A$1:$C$20,3,FALSE) for H1

The use of the $ signs changing on how the formulae might be copied.

(Now trying to figure out why the OP wanted it.)

Thanks guys,

The first one worked for me.

Phillip yours worked but it only repeated what was in column B and C on the same row as i was picking the drop down list.

Thanks

My apologies, that was the way I read your post, hence my comment on trying to figure out what you were wanting.

Vlookup and hlookup can be very useful though.

Hi. I have been following this with interest as I do a lot of Excel work but have never used INDEX and MATCH but use VLOOKUP and HLOOKUP.

I don't quite understand what is wrong with the VLOOKUP solution. If the drop down box selects an item in row 20 for example, don't you want the values and currency from the same row 20?

How is the INDEX solution different?

In this instance it doesn't make any difference. The VLOOKUP could have been:

=VLOOKUP(F1,A1:C20,3,FALSE)

(I never use absolute references unless crucial, as I find they make formulae harder to look at. Adding them is the user's discretion)


In both cases should there be duplicate values in the first column it will return the first instance from the target column. e.g.

Tom   1
Dick  2
Harry 3
Harry 4
Tom   5
Dick  6

If you were to select the second Tom (value of '5') in a DD list, your result would be '1'. Both VLOOKUP and INDEX/MATCH do this, and it's a pity. Many people (myself included) have tried to use Excel as a database, rather than a spreadsheet, and come a cropper when duplicates occur. There are ways round it though.


The main difference is that VLOOKUP can't return results from columns to the left of the lookup column.

See my example (coloured cells are the lists) - The corresponding 'G' and 'H' cells are INDEX and VLOOKUP respectively - notice the H3 problem.:

http://www.camieabz.co.uk/index.xls (97-2003 format)


More info:

http://exceluser.com/blog/455/the-limitations-of-vlo...


The other additional bonus is that I always found LOOKUP, VLOOKUP, HLOOKUP a little confusing. Similar to SUMIF & COUNTIF, unless you use them quite regularly, you tend to forget which one does what without having to refresh yourself. Index/Match takes out some of that.

Many thanks for this...I thought I was going mad as I couldn't see why both won't work.

To overcome the problem of VLOOKUP not looking left, whenever I face this situation I insert a column in the appropriate place (so it is the leftmost column in the VLOOKUP range) and make this contain the same values as the required column by linking each cell to the source cell (eg =C4)

Can't solve the duplicates problem though, unless you number each row to make it unique.