Re: lookup question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <JonathanHorvath@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:B49FBD6B-9301-4D43-A524-2CD6FA03A1BE@xxxxxxxxxxxxxxxx
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 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" <JonathanHorvath@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:017DA964-4DFA-4778-A581-1B7B1C7211DE@xxxxxxxxxxxxxxxx
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 ***, but just "tech" in the second *** in
your
example.

Hope this helps.

Pete

"Jonathan Horvath" <JonathanHorvath@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:18D48C1A-70A2-4C38-BBE1-12AE0696E671@xxxxxxxxxxxxxxxx
I have multiple tabs in a spread*** 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








.

Quantcast