I'm trying to create and If/then statement from the following table:

I have a drop down list with the values on x
I have a drop down list with the values on y
I need to populate another field with the value that corresponds to the
combination of the two.

How do I do that?

Here's the data:

1 M 10 M 20 M 30 M
1.3 Mbps 877,500 8,775,000 17,550,000 26,330,000
1.7 Mbps 1,147,500 11,475,000 22,950,000 34,425,000
2.5 Mbps 1,687,500 16,875,000 33,750,000 50,625,000

Also

Help with the following (seperate) formula:

If (X) = 1.3 mbps then (A*.878), If = 1.7 Mbps then (a*1.147), if 2.5 mbps
then (a*1.687)

Presumably x and y are two cells, returning one of the values in your
first column and one of the values in your first row. You will need
something like this:

=INDEX(B2:E4,MATCH(x,A2:A4,0),MATCH(y,B1:E1,0))

For your second query, try this:

=a*VLOOKUP(x,A2:B4,2,0)/1000000

Substitute your cell references for a, x and y.

Hope this helps.

Pete

On Nov 8, 10:28 pm, Siper1 wrote:
I'm trying to create and If/then statement from the following table:

I have a drop down list with the values on x
I have a drop down list with the values on y
I need to populate another field with the value that corresponds to the
combination of the two.

How do I do that?

Here's the data:

1 M 10 M 20 M 30 M
1.3 Mbps 877,500 8,775,000 17,550,000 26,330,000
1.7 Mbps 1,147,500 11,475,000 22,950,000 34,425,000
2.5 Mbps 1,687,500 16,875,000 33,750,000 50,625,000

Also

Help with the following (seperate) formula:

If (X) = 1.3 mbps then (A*.878), If = 1.7 Mbps then (a*1.147), if 2.5 mbps
then (a*1.687)

1. Where is the data which you want to populate?
2. Use this formula =If(X="1.3 mbps",A*.878,if(X="1.7
Mbps",a*1.147,a*1.687) This assumes that you have only three values.

AQIB RIZVI

Hi Siper
Do you mean something like this.
=IF(A1=1.3,B1*0.878,IF(A1=1.7,B1*1.147,B1*1.687))

I have replaced your (X) with cell references. This might put you on the
right track, as your request is not very clear.

HTH
Michael M

"Siper1" wrote:

I'm trying to create and If/then statement from the following table:

I have a drop down list with the values on x
I have a drop down list with the values on y
I need to populate another field with the value that corresponds to the
combination of the two.

How do I do that?

Here's the data:

1 M 10 M 20 M 30 M
1.3 Mbps 877,500 8,775,000 17,550,000 26,330,000
1.7 Mbps 1,147,500 11,475,000 22,950,000 34,425,000
2.5 Mbps 1,687,500 16,875,000 33,750,000 50,625,000

Also

Help with the following (seperate) formula:

If (X) = 1.3 mbps then (A*.878), If = 1.7 Mbps then (a*1.147), if 2.5 mbps
then (a*1.687)

I tried the formula bellow but couldn't get it to work properly:
=INDEX(B11:E13,MATCH(B18,0),MATCH(B17,0))

The range on the spreadsheet is B11:E13

1 M 10 M 20 M 30 M
1.3 877,500 8,775,000 17,550,000 26,330,000
1.7 1,147,500 11,475,000 22,950,000 34,425,000
2.5 1,687,500 16,875,000 33,750,000 50,625,000

Yes:
I have a drop down field in B18 (1.3, 1.7. 2.5)
I have a drop down field in B17 (1 M, 10 M, 20 M, 30 M)
I have a blank field that needs to be populated with the data (B19) -

Thanks for the help!

"Pete_UK" wrote:

Presumably x and y are two cells, returning one of the values in your
first column and one of the values in your first row. You will need
something like this:

=INDEX(B2:E4,MATCH(x,A2:A4,0),MATCH(y,B1:E1,0))

For your second query, try this:

=a*VLOOKUP(x,A2:B4,2,0)/1000000

Substitute your cell references for a, x and y.

Hope this helps.

Pete

On Nov 8, 10:28 pm, Siper1 wrote:
I'm trying to create and If/then statement from the following table:

I have a drop down list with the values on x
I have a drop down list with the values on y
I need to populate another field with the value that corresponds to the
combination of the two.

How do I do that?

Here's the data:

1 M 10 M 20 M 30 M
1.3 Mbps 877,500 8,775,000 17,550,000 26,330,000
1.7 Mbps 1,147,500 11,475,000 22,950,000 34,425,000
2.5 Mbps 1,687,500 16,875,000 33,750,000 50,625,000

Also

Help with the following (seperate) formula:

If (X) = 1.3 mbps then (A*.878), If = 1.7 Mbps then (a*1.147), if 2.5 mbps
then (a*1.687)

This is the formula you need in B19:

=INDEX(B11:E13,MATCH(B18,A11:A13,0),MATCH(B17,B10: E10,0))

You missed the range references in the MATCH functions.

Hope this helps.

Pete

On Nov 8, 11:46 pm, Siper1 wrote:
I tried the formula bellow but couldn't get it to work properly:
=INDEX(B11:E13,MATCH(B18,0),MATCH(B17,0))

The range on the spreadsheet is B11:E13

1 M 10 M 20 M 30 M
1.3 877,500 8,775,000 17,550,000 26,330,000
1.7 1,147,500 11,475,000 22,950,000 34,425,000
2.5 1,687,500 16,875,000 33,750,000 50,625,000

Yes:
I have a drop down field in B18 (1.3, 1.7. 2.5)
I have a drop down field in B17 (1 M, 10 M, 20 M, 30 M)
I have a blank field that needs to be populated with the data (B19) -

Thanks for the help!



"Pete_UK" wrote:
Presumably x and y are two cells, returning one of the values in your
first column and one of the values in your first row. You will need
something like this:

=INDEX(B2:E4,MATCH(x,A2:A4,0),MATCH(y,B1:E1,0))

For your second query, try this:

=a*VLOOKUP(x,A2:B4,2,0)/1000000

Substitute your cell references for a, x and y.

Hope this helps.

Pete

On Nov 8, 10:28 pm, Siper1 wrote:
I'm trying to create and If/then statement from the following table:

I have a drop down list with the values on x
I have a drop down list with the values on y
I need to populate another field with the value that corresponds to the
combination of the two.

How do I do that?

Here's the data:

1 M 10 M 20 M 30 M
1.3 Mbps 877,500 8,775,000 17,550,000 26,330,000
1.7 Mbps 1,147,500 11,475,000 22,950,000 34,425,000
2.5 Mbps 1,687,500 16,875,000 33,750,000 50,625,000

Also

Help with the following (seperate) formula:

If (X) = 1.3 mbps then (A*.878), If = 1.7 Mbps then (a*1.147), if 2.5 mbps
then (a*1.687) - Hide quoted text -

- Show quoted text -