Urgent Formula Help Required
 

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

I don't really know what you mean by:

trying to return for different combinations based on a 5x5 matrix but its
not
a simple A1xB1 to give me the solution.

But assuming that you have a 5 x 5 matrix with Rows & Columns labelled 1 - 5
in each case then try:

=VLOOKUP(A1,Sheet2!A2:F7,B1+1,0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Rob" wrote in message
...
Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I
am
trying to return for different combinations based on a 5x5 matrix but its
not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Maybe

=IF(AND(Sheet1!A1=3,Sheet1!B1=2),8,"")
I'm sure it won't be that simple but a lack of information makes being more
help difficult.

Mike

"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

You probably want a VLOOKUP.
--
David Biddulph

"Rob" wrote in message
...
Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I
am
trying to return for different combinations based on a 5x5 matrix but its
not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Thanks but I still can't seem to get that to work the results that need to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

If your data values on sheet 2 are in A1:C25, then those results are exactly
what Mike H's formula gives you.
If you have put your data values in a different place on sheet 2, then
adjust Mike's formula to look at the locations where you have put your data.

It's no good just saying: "I still can't seem to get that to work"
What data do you have where? What result did you get from the formula for
what inputs?
--
David Biddulph

"Rob" wrote in message
...
Thanks but I still can't seem to get that to work the results that need
to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little
clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell
A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a
total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one
of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it
must
return a value of 8 in C1 any ideas anyone...There is a range of
numbers I am
trying to return for different combinations based on a 5x5 matrix but
its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Rob,

You put the formula in C3 of any sheet and it will look in A1 & B1 of that
sheet and take those 2 values and match them with the values in columns A & B
of sheet 2 and if a match is found it will return column C

Mike

"Rob" wrote:

Thanks but I still can't seem to get that to work the results that need to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Rob, If I understand, the other sheet (sheet2) has column A, B, C and the
first Sheet (sheet1) has column A and B and you want C to be updated from
sheet2 by the combiniation in A&B that match. Then I would insert a column
in sheet 2 that would concatinate A and B so it would look like

A B C D
=B1&C1 5 5 25

Then sort by A

Then on shee1 , do a Vlookup in C

=VLookup(A1&B1,Sheet2!$A$1:$D$200,4,FALSE)

hope this helps

"Rob" wrote:

Thanks but I still can't seem to get that to work the results that need to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

Guys

I really do appreciate your responses but on Don's I just keep getting an
n/a result and on Mike's I just keep getting a 0 figure, I have tried to copy
an extract below of what I am working on

Impact Likelihood Score
A B C
Row 1 3 2 This is the figure I need to have completed
from a table
2 5 2 I have on another sheet, so I have a
list which I have
3 5 2 also copied below
4 3 2
5 3 2

Impact Likelihood Score So basicaly whatever is on sheet 1 above in
A&B, I need to
5 5 25 drive the score from the list here
5 4 20
4 5 20
5 3 18
4 4 19
3 5 15
5 2 17
4 3 12
3 4 12
2 5 10
5 1 16
4 2 10
3 3 11
2 4 8
1 5 5
4 1 9
3 2 8
2 3 7
1 4 4
3 1 4
2 2 6
1 3 3
2 1 2
1 2 2
1 1 1


Hope this makes sense guys and cheers again

"Don" wrote:

Rob, If I understand, the other sheet (sheet2) has column A, B, C and the
first Sheet (sheet1) has column A and B and you want C to be updated from
sheet2 by the combiniation in A&B that match. Then I would insert a column
in sheet 2 that would concatinate A and B so it would look like

A B C D
=B1&C1 5 5 25

Then sort by A

Then on shee1 , do a Vlookup in C

=VLookup(A1&B1,Sheet2!$A$1:$D$200,4,FALSE)

hope this helps

"Rob" wrote:

Thanks but I still can't seem to get that to work the results that need to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use

Urgent Formula Help Required
 

The easiest way to accomplish this is to concatenate your individual ratings
on sheet 2 (before your score). So it would look like this
A B C D
5 5 55 25

Then on sheet 1 in C1 do a formula =VLOOKUP(A1&B1,Sheet2!$C$1:$D$200,2,FALSE)

It will look up your concatenated scores. Hope this helps.

"Rob" wrote:

Guys

I really do appreciate your responses but on Don's I just keep getting an
n/a result and on Mike's I just keep getting a 0 figure, I have tried to copy
an extract below of what I am working on

Impact Likelihood Score
A B C
Row 1 3 2 This is the figure I need to have completed
from a table
2 5 2 I have on another sheet, so I have a
list which I have
3 5 2 also copied below
4 3 2
5 3 2

Impact Likelihood Score So basicaly whatever is on sheet 1 above in
A&B, I need to
5 5 25 drive the score from the list here
5 4 20
4 5 20
5 3 18
4 4 19
3 5 15
5 2 17
4 3 12
3 4 12
2 5 10
5 1 16
4 2 10
3 3 11
2 4 8
1 5 5
4 1 9
3 2 8
2 3 7
1 4 4
3 1 4
2 2 6
1 3 3
2 1 2
1 2 2
1 1 1


Hope this makes sense guys and cheers again

"Don" wrote:

Rob, If I understand, the other sheet (sheet2) has column A, B, C and the
first Sheet (sheet1) has column A and B and you want C to be updated from
sheet2 by the combiniation in A&B that match. Then I would insert a column
in sheet 2 that would concatinate A and B so it would look like

A B C D
=B1&C1 5 5 25

Then sort by A

Then on shee1 , do a Vlookup in C

=VLookup(A1&B1,Sheet2!$A$1:$D$200,4,FALSE)

hope this helps

"Rob" wrote:

Thanks but I still can't seem to get that to work the results that need to
go into C3 are sitting on another worksheet so if I put

5 in A1 and 3 in B1 how do I get a return of 18 in C1

or

3 in A1 and 3 in B1 the return in C1 needs to be 11 etc etc

Cheers again guys for any help

"Mike H" wrote:

Because the conbimnations are unique you can use sumproduct

=SUMPRODUCT((Sheet2!A1:A25=A1)*(Sheet2!B1:B25=B1)* (Sheet2!C1:C25))

Mike

"Rob" wrote:


Hi guys

Thanks for your responses hopefully this mau make things a little clearer

In one worksheet I am going to be putting in numbers (1-5) in say cell A1,
and then numbers (1-5) in B1, the outcome of these needs to give me a total
in C1 which is sitting on another worksheet, so for example
A1 B1 The figure that needs to go in to C1
5 5 25
5 4 20
5 3 18
5 2 17
5 1 16
4 5 20
4 4 19
4 3 12
4 2 10
4 1 9
3 5 15
3 4 12
3 3 11
3 2 8
3 1 4
2 5 10
2 4 8
2 3 7
2 2 6
2 1 2
1 5 5
1 4 4
1 3 3
1 2 2
1 1 1

The figures themselves may well keep changing but will always been one of
the above combinations but it needs to return the value in C1

Thanks guys


"Rob" wrote:

Hi

I am trying to work out a formula where say for example

A1 3
B1 2

In a different sheet I have a table that says for this combination it must
return a value of 8 in C1 any ideas anyone...There is a range of numbers I am
trying to return for different combinations based on a 5x5 matrix but its not
a simple A1xB1 to give me the solution.

any help would be of use