| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multipl HLOOKUP/VLOOKUP Criteria
Hi,
I have a table with releases and month submitted information. I also have a separate table that is populated with "control" data that is defined on a monthly basis. What I would like to do is add a column to the first table that will auto-populate with the control value from the second table that corresponds to the release and month submitted data from the first table. Here is an illustration of the tables: Table 1 release month submitted release 1.2.ab oct release 2.3.gh nov release 1.2.ab dec release 2.3.gh oct release 1.2.ab nov release 2.3.gh dec Table 2 control table oct nov dec release 1.2.ab 8 5 1 release 2.3.gh 2 3 6 If I can create the correct formula, Table 1 would look like this: release month submitted control data release 1.2.ab oct 8 release 2.3.gh nov 3 release 1.2.ab dec 1 release 2.3.gh oct 2 release 1.2.ab nov 5 release 2.3.gh dec 6 I do not know if it is possible to next HLOOKUP and VLOOKUP functions or if there is a better way to do this. Any help is much appreciated. thanks! Jon |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multipl HLOOKUP/VLOOKUP Criteria
Hi Jonathan
Try =INDEX(Sheet2!$A$1:$D$3, MATCH(Sheet1!A2,Sheet2!$A$1:$A$3,0), MATCH(Sheet1!B2,Sheet2!$A$1:$D$1,0)) -- Regards Roger Govier "Jonathan Horvath" wrote in message ... Hi, I have a table with releases and month submitted information. I also have a separate table that is populated with "control" data that is defined on a monthly basis. What I would like to do is add a column to the first table that will auto-populate with the control value from the second table that corresponds to the release and month submitted data from the first table. Here is an illustration of the tables: Table 1 release month submitted release 1.2.ab oct release 2.3.gh nov release 1.2.ab dec release 2.3.gh oct release 1.2.ab nov release 2.3.gh dec Table 2 control table oct nov dec release 1.2.ab 8 5 1 release 2.3.gh 2 3 6 If I can create the correct formula, Table 1 would look like this: release month submitted control data release 1.2.ab oct 8 release 2.3.gh nov 3 release 1.2.ab dec 1 release 2.3.gh oct 2 release 1.2.ab nov 5 release 2.3.gh dec 6 I do not know if it is possible to next HLOOKUP and VLOOKUP functions or if there is a better way to do this. Any help is much appreciated. thanks! Jon |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multipl HLOOKUP/VLOOKUP Criteria
Assuming table2 is Sheet2!A1:M20
Sheet1!D2: = INDEX(Sheet2!$A$2:$M$10,MATCH(A2,Sheet2!$A:$A,0),M ATCH(B2,Sheet2!$A:$A,0)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jonathan Horvath" wrote in message ... Hi, I have a table with releases and month submitted information. I also have a separate table that is populated with "control" data that is defined on a monthly basis. What I would like to do is add a column to the first table that will auto-populate with the control value from the second table that corresponds to the release and month submitted data from the first table. Here is an illustration of the tables: Table 1 release month submitted release 1.2.ab oct release 2.3.gh nov release 1.2.ab dec release 2.3.gh oct release 1.2.ab nov release 2.3.gh dec Table 2 control table oct nov dec release 1.2.ab 8 5 1 release 2.3.gh 2 3 6 If I can create the correct formula, Table 1 would look like this: release month submitted control data release 1.2.ab oct 8 release 2.3.gh nov 3 release 1.2.ab dec 1 release 2.3.gh oct 2 release 1.2.ab nov 5 release 2.3.gh dec 6 I do not know if it is possible to next HLOOKUP and VLOOKUP functions or if there is a better way to do this. Any help is much appreciated. thanks! Jon |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multipl HLOOKUP/VLOOKUP Criteria
Hi Bob and Roger,
That did it thanks! Jon "Bob Phillips" wrote: Assuming table2 is Sheet2!A1:M20 Sheet1!D2: = INDEX(Sheet2!$A$2:$M$10,MATCH(A2,Sheet2!$A:$A,0),M ATCH(B2,Sheet2!$A:$A,0)) -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Jonathan Horvath" wrote in message ... Hi, I have a table with releases and month submitted information. I also have a separate table that is populated with "control" data that is defined on a monthly basis. What I would like to do is add a column to the first table that will auto-populate with the control value from the second table that corresponds to the release and month submitted data from the first table. Here is an illustration of the tables: Table 1 release month submitted release 1.2.ab oct release 2.3.gh nov release 1.2.ab dec release 2.3.gh oct release 1.2.ab nov release 2.3.gh dec Table 2 control table oct nov dec release 1.2.ab 8 5 1 release 2.3.gh 2 3 6 If I can create the correct formula, Table 1 would look like this: release month submitted control data release 1.2.ab oct 8 release 2.3.gh nov 3 release 1.2.ab dec 1 release 2.3.gh oct 2 release 1.2.ab nov 5 release 2.3.gh dec 6 I do not know if it is possible to next HLOOKUP and VLOOKUP functions or if there is a better way to do this. Any help is much appreciated. thanks! Jon |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| self-sizing adv.filter criteria range | Excel Discussion (Misc queries) | |||
| Multple criteria dilemma | Excel Worksheet Functions | |||
| Return single value on multipl criteria lookup | Excel Worksheet Functions | |||
| Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
| sorting more than 3 keys | Excel Discussion (Misc queries) | |||
Linear Mode
