USERS CODE 1 MONTHLY CODE 2 3 PTMS CODE 3 ANNUAL
1 1 197 2 667 3 1597
2 1 167 2 557 3
1467
3 1 137 2 467 3 1197
4 1 117 2 407 3
1067
5 1 107 2 357 3
927
6 1 107 2 357 3
927
7 1 107 2 357 3
927
8 1 107 2 357 3
927
9 1 107 2 357 3
927
10 1 97 2 327 3
797

The above is a table of information which I wish to retrieve to sheet 2.
Sheet 2 looks like the following.
CUST CUSTOMER NUMBER SALES
DATE CODE PAY TYPE NAME OF USERS PRICE PER USER
6/16/09 1 MO JONES INC 3

I would like to retrieve the value for code 1 with 3 users, therefore, the
value should be 137. What would the SALES PRICE PER USER function or formula
be for any code 1-3 matched to number of users 1-10?

Thanks for any help with the above.

--
GRK

Try the below which will retrive the value for code 1 with 3 users..assuming
the value is in Col C ..

=INDEX(C1:C100,MATCH(1,(A1:A100=3)*(B1:B100=1),0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"GRK" wrote:

USERS CODE 1 MONTHLY CODE 2 3 PTMS CODE 3 ANNUAL
1 1 197 2 667 3 1597
2 1 167 2 557 3
1467
3 1 137 2 467 3 1197
4 1 117 2 407 3
1067
5 1 107 2 357 3
927
6 1 107 2 357 3
927
7 1 107 2 357 3
927
8 1 107 2 357 3
927
9 1 107 2 357 3
927
10 1 97 2 327 3
797

The above is a table of information which I wish to retrieve to sheet 2.
Sheet 2 looks like the following.
CUST CUSTOMER NUMBER SALES
DATE CODE PAY TYPE NAME OF USERS PRICE PER USER
6/16/09 1 MO JONES INC 3

I would like to retrieve the value for code 1 with 3 users, therefore, the
value should be 137. What would the SALES PRICE PER USER function or formula
be for any code 1-3 matched to number of users 1-10?

Thanks for any help with the above.

--
GRK

This is exactly what I need to do also - I can get your formula below to
work, it does pull the value for code 1 with 3 users.

But is there a generic formula that would pull the value for any code 1 thru
3 matching to any # of users 1-10? I would like to put the code number in
one column and the the user # in another column and the formula to always
find the correct value as the code and user # change.


"Jacob Skaria" wrote:

Try the below which will retrive the value for code 1 with 3 users..assuming
the value is in Col C ..

=INDEX(C1:C100,MATCH(1,(A1:A100=3)*(B1:B100=1),0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"GRK" wrote:

USERS CODE 1 MONTHLY CODE 2 3 PTMS CODE 3 ANNUAL
1 1 197 2 667 3 1597
2 1 167 2 557 3
1467
3 1 137 2 467 3 1197
4 1 117 2 407 3
1067
5 1 107 2 357 3
927
6 1 107 2 357 3
927
7 1 107 2 357 3
927
8 1 107 2 357 3
927
9 1 107 2 357 3
927
10 1 97 2 327 3
797

The above is a table of information which I wish to retrieve to sheet 2.
Sheet 2 looks like the following.
CUST CUSTOMER NUMBER SALES
DATE CODE PAY TYPE NAME OF USERS PRICE PER USER
6/16/09 1 MO JONES INC 3

I would like to retrieve the value for code 1 with 3 users, therefore, the
value should be 137. What would the SALES PRICE PER USER function or formula
be for any code 1-3 matched to number of users 1-10?

Thanks for any help with the above.

--
GRK

Replace the 3 and 1 with any cell reference....in the below i have changed
that to J1 and K1. Chagne to suit your requirement

=INDEX(C1:C100,MATCH(1,(A1:A100=J1)*(B1:B100=K1),0 ))

--
If this post helps click Yes
---------------
Jacob Skaria


"ckg" wrote:

This is exactly what I need to do also - I can get your formula below to
work, it does pull the value for code 1 with 3 users.

But is there a generic formula that would pull the value for any code 1 thru
3 matching to any # of users 1-10? I would like to put the code number in
one column and the the user # in another column and the formula to always
find the correct value as the code and user # change.


"Jacob Skaria" wrote:

Try the below which will retrive the value for code 1 with 3 users..assuming
the value is in Col C ..

=INDEX(C1:C100,MATCH(1,(A1:A100=3)*(B1:B100=1),0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"GRK" wrote:

USERS CODE 1 MONTHLY CODE 2 3 PTMS CODE 3 ANNUAL
1 1 197 2 667 3 1597
2 1 167 2 557 3
1467
3 1 137 2 467 3 1197
4 1 117 2 407 3
1067
5 1 107 2 357 3
927
6 1 107 2 357 3
927
7 1 107 2 357 3
927
8 1 107 2 357 3
927
9 1 107 2 357 3
927
10 1 97 2 327 3
797

The above is a table of information which I wish to retrieve to sheet 2.
Sheet 2 looks like the following.
CUST CUSTOMER NUMBER SALES
DATE CODE PAY TYPE NAME OF USERS PRICE PER USER
6/16/09 1 MO JONES INC 3

I would like to retrieve the value for code 1 with 3 users, therefore, the
value should be 137. What would the SALES PRICE PER USER function or formula
be for any code 1-3 matched to number of users 1-10?

Thanks for any help with the above.

--
GRK

That is what my first guess was also. But this formula only works for the
values in cells B1:B100, which in this example is just for "code 1". What
happens if you are looking for a number that is a code 2? Then the forumla
needs to move its reference cells from B1:B100 over to D1:D100 in order to
pull the correct amount based on the number of users.

I want it to find the value when both code number AND # of users
change......I want it to look at a table and look up an X value on the X axis
and look up a Y value on the Y axis and give me the number in the cell where
those two intersect. Maybe this is so simple I am overlooking something.

Any help would be appreciated - it seems like this would be something that
excel would handle....

"Jacob Skaria" wrote:

Replace the 3 and 1 with any cell reference....in the below i have changed
that to J1 and K1. Chagne to suit your requirement

=INDEX(C1:C100,MATCH(1,(A1:A100=J1)*(B1:B100=K1),0 ))

--
If this post helps click Yes
---------------
Jacob Skaria


"ckg" wrote:

This is exactly what I need to do also - I can get your formula below to
work, it does pull the value for code 1 with 3 users.

But is there a generic formula that would pull the value for any code 1 thru
3 matching to any # of users 1-10? I would like to put the code number in
one column and the the user # in another column and the formula to always
find the correct value as the code and user # change.


"Jacob Skaria" wrote:

Try the below which will retrive the value for code 1 with 3 users..assuming
the value is in Col C ..

=INDEX(C1:C100,MATCH(1,(A1:A100=3)*(B1:B100=1),0))

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"GRK" wrote:

USERS CODE 1 MONTHLY CODE 2 3 PTMS CODE 3 ANNUAL
1 1 197 2 667 3 1597
2 1 167 2 557 3
1467
3 1 137 2 467 3 1197
4 1 117 2 407 3
1067
5 1 107 2 357 3
927
6 1 107 2 357 3
927
7 1 107 2 357 3
927
8 1 107 2 357 3
927
9 1 107 2 357 3
927
10 1 97 2 327 3
797

The above is a table of information which I wish to retrieve to sheet 2.
Sheet 2 looks like the following.
CUST CUSTOMER NUMBER SALES
DATE CODE PAY TYPE NAME OF USERS PRICE PER USER
6/16/09 1 MO JONES INC 3

I would like to retrieve the value for code 1 with 3 users, therefore, the
value should be 137. What would the SALES PRICE PER USER function or formula
be for any code 1-3 matched to number of users 1-10?

Thanks for any help with the above.

--
GRK