Match - not sure how to use this function
in [Functions]
|
From: Luvable Lady Luvable on 5 May 2010 07:00 I have created a Validation List called Suppliers, and then once I have picked a supplier I want it to pull over the correct Account Code. An example of the list I have is: A1 B1 1 Fred Bloggs C0011 2 Snoopy C0022 3 Donald Duck C0033 4 Mickey Mouse C0044 5 Pluto C0055 6 Danger Mouse C0066 So when I pick from the drop down list (which is on another sheet) Fred Bloggs, I want it to pull over the code C0011 in a separate cell. Not sure if I have made this clear or not, but any help will be appreciated. I tried a nested if function but it wouldn't allow me too many nested functions.
From: Jacob Skaria on 5 May 2010 07:16 Check out help on VLOOKUP() Suppose you have data in Sheet1 and the query supplier in Sheet2 cell C1. In Sheet2 cell D1 enter the formula =VLOOKUP(C1,Sheet1!A:B,2,0) This returns an error if the entry in C1 is not in Sheet1 ColA. You can handle that using ISNA() IF() combination as below. =IF(ISNA(VLOOKUP(C1,Sheet1!A:B,2,0)),"",VLOOKUP(C1,Sheet1!A:B,2,0)) -- Jacob (MVP - Excel) "Luvable Lady" wrote: > I have created a Validation List called Suppliers, and then once I have > picked a supplier I want it to pull over the correct Account Code. An > example of the list I have is: > A1 B1 > 1 Fred Bloggs C0011 > 2 Snoopy C0022 > 3 Donald Duck C0033 > 4 Mickey Mouse C0044 > 5 Pluto C0055 > 6 Danger Mouse C0066 > > So when I pick from the drop down list (which is on another sheet) Fred > Bloggs, I want it to pull over the code C0011 in a separate cell. Not sure > if I have made this clear or not, but any help will be appreciated. I tried > a nested if function but it wouldn't allow me too many nested functions. >
From: Luvable Lady on 5 May 2010 07:45 Thank you Jacob for both giving me the solution and also your prompt reply, I have been working on that all morning, and I tried the VLOOKUP but just couldn't get it right. It now works thanks to your formula! "Jacob Skaria" wrote: > Check out help on VLOOKUP() > > Suppose you have data in Sheet1 and the query supplier in Sheet2 cell C1. In > Sheet2 cell D1 enter the formula > > =VLOOKUP(C1,Sheet1!A:B,2,0) > > This returns an error if the entry in C1 is not in Sheet1 ColA. You can > handle that > using ISNA() IF() combination as below. > > =IF(ISNA(VLOOKUP(C1,Sheet1!A:B,2,0)),"",VLOOKUP(C1,Sheet1!A:B,2,0)) > > -- > Jacob (MVP - Excel) > > > "Luvable Lady" wrote: > > > I have created a Validation List called Suppliers, and then once I have > > picked a supplier I want it to pull over the correct Account Code. An > > example of the list I have is: > > A1 B1 > > 1 Fred Bloggs C0011 > > 2 Snoopy C0022 > > 3 Donald Duck C0033 > > 4 Mickey Mouse C0044 > > 5 Pluto C0055 > > 6 Danger Mouse C0066 > > > > So when I pick from the drop down list (which is on another sheet) Fred > > Bloggs, I want it to pull over the code C0011 in a separate cell. Not sure > > if I have made this clear or not, but any help will be appreciated. I tried > > a nested if function but it wouldn't allow me too many nested functions. > >
From: Bob Phillips on 5 May 2010 08:37 You could also use =INDEX(Sheet1!B:B,MATCH(C1,Sheet1!A:A,0)) -- HTH Bob "Luvable Lady" <LuvableLady(a)discussions.microsoft.com> wrote in message news:7A687E5E-FD33-4ECC-B7C0-A5B6ED15F49C(a)microsoft.com... > Thank you Jacob for both giving me the solution and also your prompt > reply, I > have been working on that all morning, and I tried the VLOOKUP but just > couldn't get it right. > > It now works thanks to your formula! > > "Jacob Skaria" wrote: > >> Check out help on VLOOKUP() >> >> Suppose you have data in Sheet1 and the query supplier in Sheet2 cell C1. >> In >> Sheet2 cell D1 enter the formula >> >> =VLOOKUP(C1,Sheet1!A:B,2,0) >> >> This returns an error if the entry in C1 is not in Sheet1 ColA. You can >> handle that >> using ISNA() IF() combination as below. >> >> =IF(ISNA(VLOOKUP(C1,Sheet1!A:B,2,0)),"",VLOOKUP(C1,Sheet1!A:B,2,0)) >> >> -- >> Jacob (MVP - Excel) >> >> >> "Luvable Lady" wrote: >> >> > I have created a Validation List called Suppliers, and then once I have >> > picked a supplier I want it to pull over the correct Account Code. An >> > example of the list I have is: >> > A1 B1 >> > 1 Fred Bloggs C0011 >> > 2 Snoopy C0022 >> > 3 Donald Duck C0033 >> > 4 Mickey Mouse C0044 >> > 5 Pluto C0055 >> > 6 Danger Mouse C0066 >> > >> > So when I pick from the drop down list (which is on another sheet) Fred >> > Bloggs, I want it to pull over the code C0011 in a separate cell. Not >> > sure >> > if I have made this clear or not, but any help will be appreciated. I >> > tried >> > a nested if function but it wouldn't allow me too many nested >> > functions. >> >
|
Pages: 1 Prev: Excel 2007 Pivot Tables compatible with 2003 Next: Conditional Formatting |