Help Please, so confused now.

[Guest]

I am trying to link data from 2 worksheets. On main page I am using
M1 as Product and a54 thru a200 as type. On cost list b1:b200 are Product
names and A2:A200 is type. I need this to look up M1 with A54 on main tab
and match with indexing product and type returning result in b9. In the
cells it looks something like this:

Main
M1 Bolt



A54 Corse 0
A55 Threaded 0
A56 Long 0

Costlist
Bolt Short bolt Long Bolt Med Bolt
Corse 1/2 1/* 2/3 6
Threaded 3 2/4 0 1/2
Long 3/6 1/2 1/* 3/5


I have tried index etc and match. No luck. I need it to return what ever
text is in the corresponding boxes. ie match M1 with bolt, A54 with long and
return text found in a59 ie 3/6.

I keep getting error with match etc. I am at work so cant remember exact
test but is asked if b9 is blank, indexb1:a200, match
Main!M1,CostlistB1:b200,0 match main!A54,CostListA2:a200,0. I need to be able
to copy this down main a54:a200 so it gives me the text in d9 reguardless if
its 0, 1, 1/3 ,1/* etc.

I am just learning programming so be gentle. thank you in advance if you
can provide formula

 

[Guest]

=index(CostList!$A$1:$E$200,Match($A54,CostList!$A$1:$A$200,0),Match($M$1,CostList!$A$1:$E$1,0))

 

[Guest]

Thanks tom so much. That fixed it. Now if I can ask another one related to
this.
We have a work sheet that is like following:

1 2 3 4 5
150 13 12 11 10 9
149 12 11 10 9 8
148 11 10 9 8 7
147 10 9 8 7 6
146 9 8 7 6 5

To figure a bonus we work off 150, SO if some one did 175 how do I write
the code to make it subtract the 150 from the 175 and seek the table then
in different window put results of 25 for it to seek info to see if bonus can
be put out. There are some that do 300+ so really neeed this in 3 windows
to five the results in the colomn.

I would like to use this formula as I know it works and then have a block to
put the persons date and resluts kind of liek this.

Tom 301

result 13 result 13 result 0 if tha ti swhat it does.

Thanks again

 

[Guest]

Thanks that works fine.

=index(CostList!$A$1:$E$200,Match($A54,CostList!$A$1:$A$200,0),Match($M$1,CostList!$A$1:$E$1,0))