Nested if/then or array???
in [Functions]
|
From: Karin B Karin on 3 Jul 2008 10:55 I've been using the vlookup function to reference a cell value (Sofa, Loveseat, Chair, etc), find it in an existing list/chart and return the standard value in a given column. Ex: =vlookup(r3, Lookups!A4:Z27,19,False) Works great. I've run into trouble however when in certain places I FIRST need to know which of three OTHER cells have been selected. (Ex: Fabric, Leather, Lthr Combo) The one selected will change which column I need to reference in the vlookup. In other words this becomes instead of just "Sofa" - now it's a "Fabric, Sofa" or "Leather, Sofa" or "Lthr Combo, Sofa". In my formula cell I need to first know which cell was selected of the three above and then reference the cell that is hooked up to the vlookup list/chart. If fabric is selected I will need to look at column 13 not 19, leather 14 not 19, etc..... I can't seem to nest the if/thens correctly - or am needing to take a different approach altogether. If you can be of any assistance your help would be much appreciated. Or if I can try and provide you further detail. Thanks so much.
From: Bob Phillips on 3 Jul 2008 11:26 =vlookup(r3, Lookups!A4:Z27,IF(fabric,13,IF(leather,14,19)),False) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Karin B" <Karin B(a)discussions.microsoft.com> wrote in message news:FBAD2ECA-782F-4374-B572-E5B489C79CBA(a)microsoft.com... > I've been using the vlookup function to reference a cell value (Sofa, > Loveseat, Chair, etc), find it in an existing list/chart and return the > standard value in a given column. Ex: =vlookup(r3, > Lookups!A4:Z27,19,False) > Works great. > > I've run into trouble however when in certain places I FIRST need to know > which of three OTHER cells have been selected. (Ex: Fabric, Leather, > Lthr > Combo) The one selected will change which column I need to reference in > the > vlookup. In other words this becomes instead of just "Sofa" - now it's a > "Fabric, Sofa" or "Leather, Sofa" or "Lthr Combo, Sofa". > > In my formula cell I need to first know which cell was selected of the > three > above and then reference the cell that is hooked up to the vlookup > list/chart. If fabric is selected I will need to look at column 13 not > 19, > leather 14 not 19, etc..... > > I can't seem to nest the if/thens correctly - or am needing to take a > different approach altogether. If you can be of any assistance your help > would be much appreciated. Or if I can try and provide you further > detail. > > Thanks so much.
From: Karin B on 3 Jul 2008 12:45 Bob your brilliant!!! Can't thank you enough!!!! Have a great day! Karin "Bob Phillips" wrote: > =vlookup(r3, Lookups!A4:Z27,IF(fabric,13,IF(leather,14,19)),False) > > > -- > HTH > > Bob > > (there's no email, no snail mail, but somewhere should be gmail in my addy) > > "Karin B" <Karin B(a)discussions.microsoft.com> wrote in message > news:FBAD2ECA-782F-4374-B572-E5B489C79CBA(a)microsoft.com... > > I've been using the vlookup function to reference a cell value (Sofa, > > Loveseat, Chair, etc), find it in an existing list/chart and return the > > standard value in a given column. Ex: =vlookup(r3, > > Lookups!A4:Z27,19,False) > > Works great. > > > > I've run into trouble however when in certain places I FIRST need to know > > which of three OTHER cells have been selected. (Ex: Fabric, Leather, > > Lthr > > Combo) The one selected will change which column I need to reference in > > the > > vlookup. In other words this becomes instead of just "Sofa" - now it's a > > "Fabric, Sofa" or "Leather, Sofa" or "Lthr Combo, Sofa". > > > > In my formula cell I need to first know which cell was selected of the > > three > > above and then reference the cell that is hooked up to the vlookup > > list/chart. If fabric is selected I will need to look at column 13 not > > 19, > > leather 14 not 19, etc..... > > > > I can't seem to nest the if/thens correctly - or am needing to take a > > different approach altogether. If you can be of any assistance your help > > would be much appreciated. Or if I can try and provide you further > > detail. > > > > Thanks so much. > > >
From: Bob Phillips on 3 Jul 2008 13:18 "Karin B" <KarinB(a)discussions.microsoft.com> wrote in message news:1920BC83-F4CF-4B86-ABA4-EC1E8AC7C40A(a)microsoft.com... > Bob your brilliant!!! Can't thank you enough!!!! Have a great day! > I will now <bg>
|
Pages: 1 Prev: convert 80521 to 05/21/08 Next: If a date range contains a leap year (date) |