Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the
,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column C is the reference column and the ,3, refers to column E; is there a way to refer to a column to the left of the reference column i.e. column A? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Hi,
I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column C is the reference column and the ,3, refers to column E; is there a way to refer to a column to the left of the reference column i.e. column A? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
=VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE)
Here's what your formula means in plain English: Look for the value of cell B3 in the range Master!A3:A4180 and, if found, return the value from the corresponding cell in Master!C3:C4180. Is that what you had in mind? -- Biff Microsoft Excel MVP "ksean" wrote in message ... When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Please let me rework my question and correct my table with the following
example I have three sheets of data, the first sheet is named MASTER the second sheet is named KIDS and the third sheet is named PARENTS On the MASTER sheet I have a database of kids names complete with addresses and parents names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kids names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parents names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.s from column E on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column C is the reference column and the ,3, refers to column E; is there a way to refer to a column to the left of the reference column i.e. column A? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
I understand the parts that you have referenced but I am still missing
something here. What about the '3' ? Please look ant my 2nd attempt at explaining my delemma. Thanks ksean "T. Valko" wrote: =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) Here's what your formula means in plain English: Look for the value of cell B3 in the range Master!A3:A4180 and, if found, return the value from the corresponding cell in Master!C3:C4180. Is that what you had in mind? -- Biff Microsoft Excel MVP "ksean" wrote in message ... When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
What about the '3'?
The '3' is the third column. Your range is from column A to column O. Excel simply starts counting from the first column in your range. So column A is 1, column B is 2 and column C is 3. That's why your Vlookup will return the value in column C. Regards, Fred. "ksean" wrote in message ... I understand the parts that you have referenced but I am still missing something here. What about the '3' ? Please look ant my 2nd attempt at explaining my delemma. Thanks ksean "T. Valko" wrote: =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) Here's what your formula means in plain English: Look for the value of cell B3 in the range Master!A3:A4180 and, if found, return the value from the corresponding cell in Master!C3:C4180. Is that what you had in mind? -- Biff Microsoft Excel MVP "ksean" wrote in message ... When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
=VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE)
What about the '3' ? The table array is A3:O4180. The 3 refers to the 3rd column relative to the table array. The 3rd column of the table array is column C, C3:C4180. Please look ant my 2nd attempt at explaining my delemma. See my other reply there. -- Biff Microsoft Excel MVP "ksean" wrote in message ... I understand the parts that you have referenced but I am still missing something here. What about the '3' ? Please look ant my 2nd attempt at explaining my delemma. Thanks ksean "T. Valko" wrote: =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) Here's what your formula means in plain English: Look for the value of cell B3 in the range Master!A3:A4180 and, if found, return the value from the corresponding cell in Master!C3:C4180. Is that what you had in mind? -- Biff Microsoft Excel MVP "ksean" wrote in message ... When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Enter this array formula** on the Parents sheet in cell D2:
=INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
WOW... this formula is absolutly AWESOME!!
I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Is there anything I should watch for when I
start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Well... I am now in way over my head. :(
The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e E, I and M) and return with a '----' if it cannot find a solution? You matched the data in columns F & G but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns (E, I and M) and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Arrays and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
You'll have to refresh my memory! I'm gettin old!
I'm a bit confused over this: search three separate columns (i.e 'E', 'I' and 'M') I need to match data that may be in columns F&G or J&K or N&O So, which columns do you need to search? Is this an "or" condition? For example, the lookup value might be in any one of 3 columns. It might be in column E or it might be in column I or it might be in column M. And, the lookup value will not be in more than 1 of those columns -- Biff Microsoft Excel MVP "ksean" wrote in message ... Well... I am now in way over my head. :( The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e 'E', 'I' and 'M') and return with a '----' if it cannot find a solution? You matched the data in columns 'F' & 'G' but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns ('E', 'I' and 'M') and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Array's and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
Yes I believe that this is an 'OR' condition and the value will NOT be in
more than one location. The original 'Parents' sheet I sent had column E = Identifier, F = Parents last name and G = parents first name using 3 columns for his information. In actuality my sheet has 3 sets of this information side by side using up 9 columns. First group of 3 columns (E,F,G) pertains to parent #1, second group of 3 columns (I,J,K) pertains to parent #2 and the 3rd group of 3 columns (M,N,O) pertains to parent #3. This format of columns allows me to deal with children who have divorced parents. Your formula allowed me to search for a parent in the first group of 3 columns and attach the Childs address from Column 'D' on the 'Master' sheet I need the formula to search all three groups of 3 columns in order to obtain the answer and if there is no answer then respond with "----". I hope that better explains my prediciment if not then the only way I can think to explain it is to send you my spreadsheet and I have no idea how to do that. Thanks, Kerry "T. Valko" wrote: You'll have to refresh my memory! I'm gettin old! I'm a bit confused over this: search three separate columns (i.e 'E', 'I' and 'M') I need to match data that may be in columns F&G or J&K or N&O So, which columns do you need to search? Is this an "or" condition? For example, the lookup value might be in any one of 3 columns. It might be in column E or it might be in column I or it might be in column M. And, the lookup value will not be in more than 1 of those columns -- Biff Microsoft Excel MVP "ksean" wrote in message ... Well... I am now in way over my head. :( The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e 'E', 'I' and 'M') and return with a '----' if it cannot find a solution? You matched the data in columns 'F' & 'G' but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns ('E', 'I' and 'M') and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Array's and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
I think I'd need to see the data setup. If you want to send a copy of the
file I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is big zip it! -- Biff Microsoft Excel MVP "ksean" wrote in message ... Yes I believe that this is an 'OR' condition and the value will NOT be in more than one location. The original 'Parents' sheet I sent had column E = Identifier, F = Parents last name and G = parents first name using 3 columns for his information. In actuality my sheet has 3 sets of this information side by side using up 9 columns. First group of 3 columns (E,F,G) pertains to parent #1, second group of 3 columns (I,J,K) pertains to parent #2 and the 3rd group of 3 columns (M,N,O) pertains to parent #3. This format of columns allows me to deal with children who have divorced parents. Your formula allowed me to search for a parent in the first group of 3 columns and attach the Childs address from Column 'D' on the 'Master' sheet I need the formula to search all three groups of 3 columns in order to obtain the answer and if there is no answer then respond with "----". I hope that better explains my prediciment if not then the only way I can think to explain it is to send you my spreadsheet and I have no idea how to do that. Thanks, Kerry "T. Valko" wrote: You'll have to refresh my memory! I'm gettin old! I'm a bit confused over this: search three separate columns (i.e 'E', 'I' and 'M') I need to match data that may be in columns F&G or J&K or N&O So, which columns do you need to search? Is this an "or" condition? For example, the lookup value might be in any one of 3 columns. It might be in column E or it might be in column I or it might be in column M. And, the lookup value will not be in more than 1 of those columns -- Biff Microsoft Excel MVP "ksean" wrote in message ... Well... I am now in way over my head. :( The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e 'E', 'I' and 'M') and return with a '----' if it cannot find a solution? You matched the data in columns 'F' & 'G' but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns ('E', 'I' and 'M') and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Array's and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column to the left of the reference column i.e. column 'A'? MASTER sheet A B C D E F Ambeault Chelsea 1 Ambeault Kyla RR2 Anderson Cache 2 Anderson Shelley 419 Riverpark Dr Ansell Jaime 3 Ansell Dave 9609-100 Street Ansell Sean 4 Ansell Dave 3937-51 Street Antoniuk Braden 5 Antoniuk Chantal 8146-94 Avenue Arbuckle Jay 6 Arbuckle Gail 10 Westwood Wynd Banh Shally 7 Banh Tien 10 Westwood Wynd Barbour Michael 8 Barbour Dianna 438 Riverpark Dr Suggestions are appreciated Thanks ksean |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
I am sorry I do not understand where to send this spreadsheet, can you please
clarify? Thanks Kerry "T. Valko" wrote: I think I'd need to see the data setup. If you want to send a copy of the file I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is big zip it! -- Biff Microsoft Excel MVP "ksean" wrote in message ... Yes I believe that this is an 'OR' condition and the value will NOT be in more than one location. The original 'Parents' sheet I sent had column E = Identifier, F = Parents last name and G = parents first name using 3 columns for his information. In actuality my sheet has 3 sets of this information side by side using up 9 columns. First group of 3 columns (E,F,G) pertains to parent #1, second group of 3 columns (I,J,K) pertains to parent #2 and the 3rd group of 3 columns (M,N,O) pertains to parent #3. This format of columns allows me to deal with children who have divorced parents. Your formula allowed me to search for a parent in the first group of 3 columns and attach the Childs address from Column 'D' on the 'Master' sheet I need the formula to search all three groups of 3 columns in order to obtain the answer and if there is no answer then respond with "----". I hope that better explains my prediciment if not then the only way I can think to explain it is to send you my spreadsheet and I have no idea how to do that. Thanks, Kerry "T. Valko" wrote: You'll have to refresh my memory! I'm gettin old! I'm a bit confused over this: search three separate columns (i.e 'E', 'I' and 'M') I need to match data that may be in columns F&G or J&K or N&O So, which columns do you need to search? Is this an "or" condition? For example, the lookup value might be in any one of 3 columns. It might be in column E or it might be in column I or it might be in column M. And, the lookup value will not be in more than 1 of those columns -- Biff Microsoft Excel MVP "ksean" wrote in message ... Well... I am now in way over my head. :( The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e 'E', 'I' and 'M') and return with a '----' if it cannot find a solution? You matched the data in columns 'F' & 'G' but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns ('E', 'I' and 'M') and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Array's and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and column reference problems
xl can help at comcast period net
Remove the word "can". Remove all the spaces. Replace the word "at" with the at sign. Replace the word "period" with a dot. -- Biff Microsoft Excel MVP "ksean" wrote in message ... I am sorry I do not understand where to send this spreadsheet, can you please clarify? Thanks Kerry "T. Valko" wrote: I think I'd need to see the data setup. If you want to send a copy of the file I'm at: xl can help at comcast period net Remove "can" and change the obvious. If the file is big zip it! -- Biff Microsoft Excel MVP "ksean" wrote in message ... Yes I believe that this is an 'OR' condition and the value will NOT be in more than one location. The original 'Parents' sheet I sent had column E = Identifier, F = Parents last name and G = parents first name using 3 columns for his information. In actuality my sheet has 3 sets of this information side by side using up 9 columns. First group of 3 columns (E,F,G) pertains to parent #1, second group of 3 columns (I,J,K) pertains to parent #2 and the 3rd group of 3 columns (M,N,O) pertains to parent #3. This format of columns allows me to deal with children who have divorced parents. Your formula allowed me to search for a parent in the first group of 3 columns and attach the Childs address from Column 'D' on the 'Master' sheet I need the formula to search all three groups of 3 columns in order to obtain the answer and if there is no answer then respond with "----". I hope that better explains my prediciment if not then the only way I can think to explain it is to send you my spreadsheet and I have no idea how to do that. Thanks, Kerry "T. Valko" wrote: You'll have to refresh my memory! I'm gettin old! I'm a bit confused over this: search three separate columns (i.e 'E', 'I' and 'M') I need to match data that may be in columns F&G or J&K or N&O So, which columns do you need to search? Is this an "or" condition? For example, the lookup value might be in any one of 3 columns. It might be in column E or it might be in column I or it might be in column M. And, the lookup value will not be in more than 1 of those columns -- Biff Microsoft Excel MVP "ksean" wrote in message ... Well... I am now in way over my head. :( The example I provided and that you solved, very nicely I should add, requires that the formula searches one column for the identifier, I don't suppose that you could tell me how I can adapt your formula to search three separate columns (i.e 'E', 'I' and 'M') and return with a '----' if it cannot find a solution? You matched the data in columns 'F' & 'G' but I need to match data that may be in columns F&G or J&K or N&O The problem is that I am not just searching in one column for the identifier, but I am searching for the identifier in three separate columns ('E', 'I' and 'M') and if an answer cannot be found the formula needs to returns with '----'. Sorry to snowball this dilemma but I really do appreciate your help. Oh and by the way I am running Excel 2003 so thanks for the tip about Array's and whole columns. I guess I will have to stick with specific ranges until I upgrade the program. Thanks again, Ksean "T. Valko" wrote: Is there anything I should watch for when I start attempting to adapt this formula? Well, like with *any* formula the bigger the range references the longer it takes for the formula to calculate. Also, since this is an array formula you can't use entire columns as range references *unless* you're using Excel 2007. For example, you can't do this *unless* you're using Excel 2007 =INDEX(Master!D:D,MATCH(1,(Master!F:F=B2)*(Master! G:G=C2),0)) -- Biff Microsoft Excel MVP "ksean" wrote in message ... WOW... this formula is absolutly AWESOME!! I would never of thought in a million years to hit 'CTRL,SHIFT,ENTER' to activate the formula! It works really well on my small sample I just need to adapt it to my real working spreadsheets which ares substantially larger. Is there anything I should watch for when I start attempting to adapt this formula? Thanks ksean "T. Valko" wrote: Enter this array formula** on the Parents sheet in cell D2: =INDEX(Master!D$2:D$9,MATCH(1,(Master!F$2:F$9=B2)* (Master!G$2:G$9=C2),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down as needed. Note that in your sample data there are duplicate parent names with different addresses: 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave The above formula will *only* find the address for the first instance of the parent names. -- Biff Microsoft Excel MVP "ksean" wrote in message ... Please let me rework my question and correct my table with the following example I have three sheets of data, the first sheet is named "MASTER" the second sheet is named "KIDS" and the third sheet is named "PARENTS" On the MASTER sheet I have a database of kid's names complete with addresses and parent's names MASTER sheet example A B C D E F G 1 No. Last First Address No. Last First 2 1 Ambeault Chelsea RR2 3 Ambeault Kyla 3 6 Anderson Cache 419 Riverpark Dr 5 Anderson Shelley 4 11 Ansell Jaime 9609-100 Street 18 Ansell Dave 5 3 Ansell Sean 3937-51 Street 35 Ansell Dave 6 25 Antoniuk Braden 8146-94 Avenue 215 Antoniuk Chantal 7 9 Arbuckle Jay 10 Westwood Wynd 159 Arbuckle Gail 8 17 Banh Shally 10 Westwood Wynd 7 Banh Tien 9 21 Barbour Michael 438 Riverpark Dr 87 Barbour Dianna On the KIDS sheet I have a database that consists of the kid's names and addresses. The formula that I am using is =VLOOKUP(A3,master!$A$1:$O$177,2,FALSE) and it works very well. KIDS sheet example A B C D 1 No. Last First Address 2 1 Ambeault Chelsea RR2 3 3 Ansell Sean 3937-51 Street 4 6 Anderson Cache 419 Riverpark Dr 5 9 Arbuckle Jay 10 Westwood Wynd 6 11 Ansell Jaime 9609-100 Street 7 17 Banh Shally 10 Westwood Wynd 8 21 Barbour Michael 438 Riverpark Dr 9 25 Antoniuk Braden 8146-94 Avenue On the PARENTS sheet I have a database that consists of the parent's names and addresses. The formula that I am using =VLOOKUP(F3,master!$E$1:$O$177,2,FALSE) and it works very well for the names but I cannot figure out how to reference the addresses. PARENTS sheet example A B C D 1 No. Last First Address 2 3 Ambeault Kyla ? 3 5 Anderson Shelley ? 4 7 Banh Tien ? 5 18 Ansell Dave ? 6 35 Ansell Dave ? 7 87 Barbour Dianna ? 8 159 Arbuckle Gail ? 9 215 Antoniuk Chantal ? How do I get the address from the MASTER sheet to appear in the correct place on the PARENTS sheet while only referencing the PARENTS No.'s from column 'E' on the MASTER sheet? Thanks ksean PS I don't know how to fix the MASTER table so it displays better in this forum...sorry! "Mike H" wrote: Hi, I don't understand the question, in Vlookup the column that 3 refers to is 'always' the third column and the first column 'A' is always the one being looked up. Perhaps you would be better giving us the lookup value and what you expect to be returned from that but it sounds like you may mean INDEX & MATCH. Mike "ksean" wrote: When considering this formula =VLOOKUP(B3,Master!$A$3:$O$4180,3,FALSE) the ,3, refers to the second column to the right of a reference column on the Master sheet. Using this formula and the following table where column 'C' is the reference column and the ,3, refers to column 'E'; is there a way to refer to a column |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column reference problems. HELP!!! | Excel Discussion (Misc queries) | |||
using hlookup to reference a column in a vlookup formula? | Excel Worksheet Functions | |||
Column reference in Vlookup formula | Excel Worksheet Functions | |||
Vlookup with Multiple like values in the reference column | Excel Worksheet Functions | |||
VLookup using 2 worksheets, whole column reference | Excel Worksheet Functions |