Excel, action on a selected item

  • Thread starter Thread starter jablumy
  • Start date Start date
J

jablumy

lets say i have a table like

banane 0.39
apple 0.50
...

i want to have 2 colums,
1st a drop down list of the fruit... (that i know)

on the selection of one of them, i want the price to automaticall
shows on the cell beside... how do i set that cell ?

Thanks a lot !

Ja
 
Hi
if your lookup table is on sheet1 and you have created a dropdown list
on sheet two in cell A1 use
=IF(A1<>"",VLOOKUP(A1,'sheet1'!$A$1:$B$10,2,0),"")
to get the associated price
 
One way using OFFSET and MATCH
(VLOOKUP can also be used)

Assume your reference table (per sample given in the post)
is set-up in Sheet1, cols A and B, row1 down

In Sheet2
-----------
If your DV drop list is in col A, A1 down

Put in B1:

=IF(ISNA(MATCH(A1,Sheet1!$A:$A,0)),"",OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!$A:
$A,0)-1,1))

which will return blanks [ "" ] for cases where there's no match
for the item selected in col A

Copy B1 down
 
do i have to do something special to the 2 rows with my associations..
like creating a lookup table ?

because it now doesnt work... giving me ?name error

thank
 
Not really sure what happened ..

Perhaps you could elaborate a bit on how you're setting it
up, and post the actual formula used which is giving you
the error ?

IMO, both Frank and me did provide some clear steps and
assumptions to you in our suggestions

Example: Your lookup table (sample below)
is assumed in Sheet1, cols A and B, row1 down

banane 0.39
apple 0.50
etc etc

Frank's VLOOKUP suggestion assumes that your lookup table
is in Sheet1, within the range A1:B10

: = IF(A1<>"",VLOOKUP(A1,'sheet1'!$A$1:$B$10,2,0),"")

My OFFSET suggestion assumes your lookup table
is in Sheet1, cols A and B, row1 down (indefinite range)

And the formula suggested viz.:

: = IF(ISNA(MATCH(A1,Sheet1!$A:$A,0)),"",OFFSET(Sheet1!
$A$1,MATCH(A1,Sheet1!$A:$A,0)-1,1))

will also trap non matching cases
 
=IF(A2<>"",VLOOKUP(A2,LVal!$A$2:$B$6),"")

gives me ?NAME

I used A2 because i have a row of title (to get that column into a lis
for validation)

does it matter if my excel is in french ? Should not... i also tried

=SI(A2<>"",RECHERCHE(A2,LVal!A2:A6,LVal!B2:B6),"")

that tho, gives me somthething from the column B, but not the on
associate with column A.

Mmmmm... do you see something
 
I notice it is not working when not sort by alphabetical order... wha
to not have that constraint ?

if sort, it does work (THANKS !)

and something else... how to update everyhting automatically if a pric
change ? is it suppose to do it by itself ?

Thank yo
 
i have

letter position
a 1
b 2
c 3
d 4
e 5

and it work good.

if i have e,d,c,b,a associate with 5,4,3,2,1 then my results are

b #N/A
c 3
e 1
a #N/A
d 1

isnt it weird
 
Try wrapping TRIM(), especially around the lookup value in
VLOOKUP

[ suspect that some look-up values contain leading or
trailing spaces which will throw the matching off]

So try instead (based on Frank's suggestion):

=IF(TRIM(A1)<>"",VLOOKUP(TRIM(A1),'sheet1'!
$A$1:$B$10,2,0),"")

Alternatively, try:

=IF(ISNA(VLOOKUP(TRIM(A1),'sheet1'!
$A$1:$B$10,2,0)),"",VLOOKUP(TRIM(A1),'sheet1'!
$A$1:$B$10,2,0))

which will return blanks for non-matching cases
instead of #NA errors
 
I cant add any parameters to VLOOKUP... (neither the ,2 and ,0)

not normal i guess..

any idea
 
Did some searches via Google ..

Think you may be using the *wrong* French Excel translation of VLOOKUP ?

It should be RECHERCHEV(), not RECHERCHE () which is LOOKUP()

For example:

=RECHERCHEV(C2,'[Type factures.xls]Feuil1'!$C$1:$G$1185,5,FAUX)

Now you should be able to enter the column index number (i.e. 2)
and the range lookup (i.e. FALSE or "0" for an exact match)

Syntax from Excel Help:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
 
Back
Top