V-lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All

I am totally lost

I am trying to setup a dropdown list in excel,so that when i select something from the drop down list it retrieves information from other cells and places it somewhere else

E.G. I want the drop down list to appear on sheet one with country names, which i have entered in coumn F on sheet tw
Where f4 = france f5 = ireland, also on sheet two i have the french for street(RUE),city(CITE),country(PAYS),and postal code(CEDEX) in cells a4,a7 which i want to appear in cells d13,d16 on sheet one when i select france on the drop down list and when i select ireland on the drop down list i want cells d13,d16 to change to street,city,country,postal code which i have entered in in sheet two in cells b4,b7

I know i have probably made this sound more complicated but any help i can get with this would be greatly appreciated
I have tried using V-lookup but can't figure it out

Please help!!!
 
Hi
try (if column A on sheet 1 stores your drop down list) enter the
following in D13
=INDEX('sheet2'!$A$1:$A$100,MATCH(A13,'sheet2'!$F$1:$F$100,0))
 
Hi Frank

Am in ireland didn't know if you were online or not
Thank you all for your help so far, I know yer probably getting annoyed with me now

rue street Franc
cite city Irelan
pays country
cedex postcode

The Formula you gave me when i pick france it just picks rue, a4 and i need it to pick rue and the four cells underneath a4-a7 and show them on sheet 01 in cells d13-d16, also when i select ireland now it picks cite a5 and i want to pick street and the four cells underneath, b4-b7 and show them also on sheet 01 in cells d13-d16 and so on

I would be very grateful for any more help you can give me
 
Hi
not sure I undertsood your spreadsheet layout, but try the following:
cell D13:
=INDEX('sheet2'!$A$1:$A$100,MATCH(A13,'sheet2'!$F$1:$F$100,0))
cell D14:
=INDEX('sheet2'!$A$1:$A$100,MATCH(A13,'sheet2'!$F$1:$F$100+1,0))
cell D15
=INDEX('sheet2'!$A$1:$A$100,MATCH(A13,'sheet2'!$F$1:$F$100+2,0))
cell D16
=INDEX('sheet2'!$A$1:$A$100,MATCH(A13,'sheet2'!$F$1:$F$100+3,0))
 
No I'm confuses about your spreadsheet desing :-)
It makes no sense to use
=INDEX(Sheet2!$B$4,MATCH(A13,Sheet2!$F$5,0))

as the first parameter of the index function should be a range and not
a single cell. This will always return B4 or an error!
You may mail me your spreadsheet or post some example rows of sheet 1
and 2 (as I really don't know where your data is stored).
frank[dot]kabel[at]freenet[dot]de




--
Regards
Frank Kabel
Frankfurt, Germany
Jinx said:
Hi Frank,

Nearly there I think,

Where france is f4 and rue is a4 i have used the following formula =INDEX(Sheet2!$A$4,MATCH(A13,Sheet2!$F$4,0))

which returns the value i want to d 13 in sheet 01, but in order to
Click to expand...
get the value i want when i select ireland f5 and street b4
I have to change the formula to this
Click to expand...
=INDEX(Sheet2!$B$4,MATCH(A13,Sheet2!$F$5,0)), is there a formula i can
use so that when the value is ireland the values in d13 d14 d15 d16
return the values from sheet two b4 b5 b6 b7 and when the value is
france the values in d13 d14 d15 d16 return the values from a4 a5 a6 a7
 
Back
Top