Urgent Formula Help Required

R

Rob

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
 
S

Sandy Mann

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.
Click to expand...

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

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
M

Mike H

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
 
R

Rob

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
 
M

Mike H

Because the conbimnations are unique you can use sumproduct

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

Mike
 
R

Rob

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
 
D

David Biddulph

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?
 
M

Mike H

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
 
D

Don

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
 
R

Rob

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
 
P

Patty

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