I need to find out

[alex]

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 Liengme]

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

 

[alex]

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

 

[Idoia]

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.

 

[Bernard Liengme]

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),MATCH(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)

 

[Fred Smith]

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.

 

[Idoia]

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?