how to find the intersection point between a column and a row. Basically, i
have exchange rates in a column and the rates to which i want to convert are
in a row. I need to find where the column and row intersect...is there a way
to do that?

I think VLOOKUP is the answer but you have not fully explained the problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row. Basically,
i
have exchange rates in a column and the rates to which i want to convert
are
in a row. I need to find where the column and row intersect...is there a
way
to do that?

Good point Bernard, i'll try to be more specific.

I have something that looks like this.
AED USD BRL CAD EGP
USD 1.2 1.5 2 1 .5
EUR 1.5 3 .75 100 35
MAD etc...
VND
LBP
ZMK
ZAR

This is what i have to use, on another page, i have specific conversions i
need to make and dont want to do this manually every month. So in column A i
have what i need to go from to what i need to go to in column B.
I want to just be able to look those up.
Hopefully that explains it a little better.

Let me know if you can help,

Thanks,

Alex

"Bernard Liengme" wrote:

I think VLOOKUP is the answer but you have not fully explained the problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row. Basically,
i
have exchange rates in a column and the rates to which i want to convert
are
in a row. I need to find where the column and row intersect...is there a
way
to do that?

Let's say your table is in range A1:F8, column A is your "from" currency and
row 1 is your "to" currency.

The following formula gives you the conversion from EUR to CAD:

=VLOOKUP("EUR",$A$1:$F$8,MATCH("CAD",A1:F1,0),0)

Just substitute "EUR" and "CAD" to the appropriate cell reference in your
second sheet.


"alex" wrote:

Good point Bernard, i'll try to be more specific.

I have something that looks like this.
AED USD BRL CAD EGP
USD 1.2 1.5 2 1 .5
EUR 1.5 3 .75 100 35
MAD etc...
VND
LBP
ZMK
ZAR

This is what i have to use, on another page, i have specific conversions i
need to make and dont want to do this manually every month. So in column A i
have what i need to go from to what i need to go to in column B.
I want to just be able to look those up.
Hopefully that explains it a little better.

Let me know if you can help,

Thanks,

Alex

"Bernard Liengme" wrote:

I think VLOOKUP is the answer but you have not fully explained the problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row. Basically,
i
have exchange rates in a column and the rates to which i want to convert
are
in a row. I need to find where the column and row intersect...is there a
way
to do that?

I put your data on Sheet1.
In row 1 I have: (empty cell A1), AED, USD, BRL etc
In column A I have (empty cell A1), USD, EUR ...
The number 1.2 is in B2

On Sheet2 in A1, I have EUR
In B1 I have BRL
In C1 the formula
=INDEX(Sheet1!B2:K10,MATCH(A1,Sheet1!A2:A10,0),MAT CH(B1,Sheet1!B1:K1,0))
returns the value 0.75

If the row 1 and column A were different (say there was no USD in row !)
Then I could select the data and use Insert | Names. This would let me use
the intersection operator as in =EUR BRL (the space between the names is
the intersection operator)

Suppose in row 1 you had names like AEDX, USDX... and in A you had USDY,
EURY....
Then select all the data and use Insert | Names
Now we can use =USDX EURY to get the value 3

Thank goodness you data is hypothetical - look at the $CND

best wishes (happy to continue this with private email)
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
Good point Bernard, i'll try to be more specific.

I have something that looks like this.
AED USD BRL CAD EGP
USD 1.2 1.5 2 1 .5
EUR 1.5 3 .75 100 35
MAD etc...
VND
LBP
ZMK
ZAR

This is what i have to use, on another page, i have specific conversions i
need to make and dont want to do this manually every month. So in column
A i
have what i need to go from to what i need to go to in column B.
I want to just be able to look those up.
Hopefully that explains it a little better.

Let me know if you can help,

Thanks,

Alex

"Bernard Liengme" wrote:

I think VLOOKUP is the answer but you have not fully explained the
problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row.
Basically,
i
have exchange rates in a column and the rates to which i want to
convert
are
in a row. I need to find where the column and row intersect...is there
a
way
to do that?

You want Vlookup, but you also need to be able to calculate the column
number.

For example, if you wanted to convert USD, you would use something like:

=vlookup(cell,table,3,false)

because USD is in column 3. Now we just need a way to calculate the column
number. Match will do that. So try:

=vlookup(cell1,A:F,match(cell2,A1:F1,0),false)

Adjust the ranges to suit.

Regards,
Fred.

"alex" wrote in message
...
Good point Bernard, i'll try to be more specific.

I have something that looks like this.
AED USD BRL CAD EGP
USD 1.2 1.5 2 1 .5
EUR 1.5 3 .75 100 35
MAD etc...
VND
LBP
ZMK
ZAR

This is what i have to use, on another page, i have specific conversions i
need to make and dont want to do this manually every month. So in column
A i
have what i need to go from to what i need to go to in column B.
I want to just be able to look those up.
Hopefully that explains it a little better.

Let me know if you can help,

Thanks,

Alex

"Bernard Liengme" wrote:

I think VLOOKUP is the answer but you have not fully explained the
problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row.
Basically,
i
have exchange rates in a column and the rates to which i want to
convert
are
in a row. I need to find where the column and row intersect...is there
a
way
to do that?

Bernard,

I've been experimenting with your intersection operator. After naming the
ranges, using a formula like =USDX EURY works fine. The problem arises when I
don't want to type "USDY", etc into every cell, but use the "USDY" and "EURY"
that are written into cells A1 and B1 instead. Just typing =A1 B1 doesn't
work, since it does not recognise the names as ranges but as text, i guess...
how do you solve this?

"Bernard Liengme" wrote:

I put your data on Sheet1.
In row 1 I have: (empty cell A1), AED, USD, BRL etc
In column A I have (empty cell A1), USD, EUR ...
The number 1.2 is in B2

On Sheet2 in A1, I have EUR
In B1 I have BRL
In C1 the formula
=INDEX(Sheet1!B2:K10,MATCH(A1,Sheet1!A2:A10,0),MAT CH(B1,Sheet1!B1:K1,0))
returns the value 0.75

If the row 1 and column A were different (say there was no USD in row !)
Then I could select the data and use Insert | Names. This would let me use
the intersection operator as in =EUR BRL (the space between the names is
the intersection operator)

Suppose in row 1 you had names like AEDX, USDX... and in A you had USDY,
EURY....
Then select all the data and use Insert | Names
Now we can use =USDX EURY to get the value 3

Thank goodness you data is hypothetical - look at the $CND

best wishes (happy to continue this with private email)
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
Good point Bernard, i'll try to be more specific.

I have something that looks like this.
AED USD BRL CAD EGP
USD 1.2 1.5 2 1 .5
EUR 1.5 3 .75 100 35
MAD etc...
VND
LBP
ZMK
ZAR

This is what i have to use, on another page, i have specific conversions i
need to make and dont want to do this manually every month. So in column
A i
have what i need to go from to what i need to go to in column B.
I want to just be able to look those up.
Hopefully that explains it a little better.

Let me know if you can help,

Thanks,

Alex

"Bernard Liengme" wrote:

I think VLOOKUP is the answer but you have not fully explained the
problem

Try like this:
In A1:20 I have .....
In the B columns I have....
In the C columns ....

In G1 I have ..... and in H1 I want .....
Then we can be more helpful
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme



"alex" wrote in message
...
how to find the intersection point between a column and a row.
Basically,
i
have exchange rates in a column and the rates to which i want to
convert
are
in a row. I need to find where the column and row intersect...is there
a
way
to do that?