lookup question

[Jonathan Horvath]

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

 

[Pete_UK]

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]

it does, thanks!

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]

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]

You're welcome, Jonathan - thanks for feeding back.

Pete

 

[Pete_UK]

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 somewhere:

=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]

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]

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

 

[Harlan Grove]

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     product3
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.