Why is my vlookup + MATCH formula not working?

[Jyggalag]

hi all,

i have this overview:

And in my other sheet entitled "XXX" i have this table:

I am using this formula to calculate my nominal value in EUR:

=IF(J2="EUR";I2;I2*VLOOKUP(J2;'XXX'!AO6:AS7;MATCH(J2;'XXX'!AO6:AS6;0);FALSE))

it should in the case above say 12046,37 * 0,134192, since the currency is DKK and not EUR, so it needs to recalculate the value into EUR.

However, my formula just returns "#N/A"

what have I done wrong and can someone please help me fix my formula?

thanks!

 

[I_know_nuffin]

Try using INDEX

=IF(J2="EUR",I2,2*INDEX(N3:Q4,2,MATCH(J2,N3:Q3,0)))

the currency conversion was in the same sheet so you need to set the new range in the formula to point to your range for N3:Q4 and N3:Q3

MATCH gives the column offset and the 2 is the row offset

 

[Jyggalag]

Try using INDEX

=IF(J2="EUR",I2,2*INDEX(N3:Q4,2,MATCH(J2,N3:Q3,0)))

the currency conversion was in the same sheet so you need to set the new range in the formula to point to your range for N3:Q4 and N3:Q3

MATCH gives the column offset and the 2 is the row offset

My currency table starts at AO5 (cell value "Currency") and ends at AS6 (cell value "1,022008").

Your formula unfortunately does not work, or maybe I have misinterpreted it?

this is what I wrote, and it still returns #N/A

=IF(J2="EUR";I2;2*INDEX('XXX'!AO6:AS7;2;MATCH(J2;'XXX'!AO6:AS7;0)))

also tried with this to no avail:

=IF(J2="EUR";I2;2*INDEX('Intercompany loans overview'!AO6:AS6;2;MATCH(J2;'Intercompany loans overview'!AO6:AS7;0)))

 

[Fluff]

Try

=IF(J2="EUR";I2;I2*HLOOKUP(J2;'XXX'!AO6:AS7;2;FALSE))

 

[Jyggalag]

Try

=IF(J2="EUR";I2;I2*HLOOKUP(J2;'XXX'!AO6:AS7;2;FALSE))

It works! Thank you so much Fluff !!! :D

as always, my savior <3

@I_know_nuffin thank you for your time and help as well!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[I_know_nuffin]

I know it solved but does this work?

=IF(J2="EUR",I2,I2*INDEX(XXX!AO5:AS6,2,MATCH(J2,XXX!AO5:AS5,0)))