lookup question
I have multiple tabs in a spreadsheet that show the same data in different
formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
Put this formula in B4 of Sheet1:
=INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
it does, thanks!
"Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
actually for some reason, that is returning the value in the row below the
cell.... for example, if I'm looking to return product 1 tech its returning sue instead of jim. hmm.... "Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
You're welcome, Jonathan - thanks for feeding back.
Pete "Jonathan Horvath" wrote in message ... it does, thanks! "Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
Just check that the ranges in my formula match with the ranges that you are
using, in particular the $B$2:$D$4 in the first part. You should be able to type this in somewhe =INDEX(Sheet2!$B$2:$D$4,1,1) as a test - this should return data from the first row and first column of the table (i.e. jim). Pete "Jonathan Horvath" wrote in message ... actually for some reason, that is returning the value in the row below the cell.... for example, if I'm looking to return product 1 tech its returning sue instead of jim. hmm.... "Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
that did it.
the problem was that i was starting the range two rows too low in the first part (as a result the return value was from 2 rows below where i wanted it to be) "Pete_UK" wrote: Just check that the ranges in my formula match with the ranges that you are using, in particular the $B$2:$D$4 in the first part. You should be able to type this in somewhe =INDEX(Sheet2!$B$2:$D$4,1,1) as a test - this should return data from the first row and first column of the table (i.e. jim). Pete "Jonathan Horvath" wrote in message ... actually for some reason, that is returning the value in the row below the cell.... for example, if I'm looking to return product 1 tech its returning sue instead of jim. hmm.... "Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
Well, I'm glad to hear that, Jonathan. Obviously, I can only base the
formula on what I see in your post, but you seem to have got there in the end. Thanks for feeding back. Pete "Jonathan Horvath" wrote in message ... that did it. the problem was that i was starting the range two rows too low in the first part (as a result the return value was from 2 rows below where i wanted it to be) "Pete_UK" wrote: Just check that the ranges in my formula match with the ranges that you are using, in particular the $B$2:$D$4 in the first part. You should be able to type this in somewhe =INDEX(Sheet2!$B$2:$D$4,1,1) as a test - this should return data from the first row and first column of the table (i.e. jim). Pete "Jonathan Horvath" wrote in message ... actually for some reason, that is returning the value in the row below the cell.... for example, if I'm looking to return product 1 tech its returning sue instead of jim. hmm.... "Pete_UK" wrote: Put this formula in B4 of Sheet1: =INDEX(Sheet2!$B$2:$D$4,MATCH(B$1,Sheet2!A$2:$A$4, 0),MATCH($A4,Sheet2!$B$1:$D$1,0)) and then copy across and down as required. Note that the spellings have to be the same to get an exact match - you show "tech mgr" in the first sheet, but just "tech" in the second sheet in your example. Hope this helps. Pete "Jonathan Horvath" wrote in message ... I have multiple tabs in a spreadsheet that show the same data in different formats. i would like to create some lookups so that I don't have to maintain the data manually on each tab For example. Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name product 1 product 2 product 3 launch date sw release tech mgr xxx sw mgr product mgr Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. for example tab two: tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" any help? thanks, Jonathan |
lookup question
Jonathan Horvath wrote...
... Tab 1 has a side by side comparison of various products (each column has a different product with all kinds of data for the product in the rows) - such as product name, tech manager, sw manager, product manager, electrical manager, etc... product name * * *product 1 * * * *product 2 * * product 3 launch date sw release tech mgr * * * * * * * * * xxx sw mgr product mgr ... Tab two is a table of resource assignments per product. column 1 lists the products, row 1 lists the function (tech manager, sw manager, product manager, etc). the cells then liks who is assigned to each product and function. ... tech sw product mgr product 1 jim john eric product 2 sue scott jane product 3 bob deb jack what i would like to do is create a forumla in the cell in tab one that says "for the product listed in row 1, find that product on tab 2, then find the function listed in column 1 for this product and find that function in tab 2, then put the name of that person into this cell" So the 'source' data would be in the second worksheet? If so, name the complete table (including the top row of functions and the left column of products and the blank top-left cell) Tbl. Also, this would be MUCH EASIER if all the function labels were EXACTLY the same in both tables. So if the function is shown as 'tech mgr' in the first worksheet, it should appear as 'tech mgr' rather than 'tech' in the second worksheet. If not, you'd need a 3rd table with 2 columns, one for function name in the first worksheet and the other for the corresponding function name in the second worksheet. I'll assume you've made the function names the same in both worksheets. I'll assume the table in the first worksheet begins in cell A1, so 'product 1' would be in cell B1 and 'launch date' in cell A2. Tech manager for product 1, in cell B4 would be given by =VLOOKUP(C$1,Tbl,MATCH($A4,INDEX(Tbl,1,0),0),0) You could fill B4 down then the B4:B# right as far as needed. |
All times are GMT +1. The time now is 01:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com