I am using

=IF(ISNA(INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4 )),0,INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4))

in order to get the corresponding value in column B to a value inputed in
column J which should match column A. However this only works out when I
input 100 and it doesn't work for the other values. I don't know whether
this is due to the 0 values in column A (..but this is why i opted for Index
instead of lookup). The formula works out if i input the value (eg "255" )
instead of J2 but I need the equation linked to the value in J2 which is
again linked to other sheets.

A D
GL Sub Class "Transaction
Ccy Amount"
100 107,299.21
0 .00
0 .00
255 3,368,000.00
0 .00
0 .00
394 -11,210.26
0 .00
0 .00
454 5,486.00

Any help please?

Hi,

What your retirning to J2 with a formula isn't matching anyting in column A.
You can correct that but sometimes multiplying by 1 in your formula will work

=IF(ISNA(INDEX($A$5:$D$49,MATCH(J2*1,$A$5:$A$49,0) ,4)),0,INDEX($A$5:$D$49,MATCH(J2*1,$A$5:$A$49,0),4 ))

Mike



"Tang" wrote:

I am using

=IF(ISNA(INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4 )),0,INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4))

in order to get the corresponding value in column B to a value inputed in
column J which should match column A. However this only works out when I
input 100 and it doesn't work for the other values. I don't know whether
this is due to the 0 values in column A (..but this is why i opted for Index
instead of lookup). The formula works out if i input the value (eg "255" )
instead of J2 but I need the equation linked to the value in J2 which is
again linked to other sheets.

A D
GL Sub Class "Transaction
Ccy Amount"
100 107,299.21
0 .00
0 .00
255 3,368,000.00
0 .00
0 .00
394 -11,210.26
0 .00
0 .00
454 5,486.00

Any help please?

Thanks Mike, though it did not work out when I added the *1 in the equation,
it worked when I added *1 to column A!

Thanks a lot!!

"Mike H" wrote:

Hi,

What your retirning to J2 with a formula isn't matching anyting in column A.
You can correct that but sometimes multiplying by 1 in your formula will work

=IF(ISNA(INDEX($A$5:$D$49,MATCH(J2*1,$A$5:$A$49,0) ,4)),0,INDEX($A$5:$D$49,MATCH(J2*1,$A$5:$A$49,0),4 ))

Mike



"Tang" wrote:

I am using

=IF(ISNA(INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4 )),0,INDEX($A$5:$D$49,MATCH(J2,$A$5:$A$49,0),4))

in order to get the corresponding value in column B to a value inputed in
column J which should match column A. However this only works out when I
input 100 and it doesn't work for the other values. I don't know whether
this is due to the 0 values in column A (..but this is why i opted for Index
instead of lookup). The formula works out if i input the value (eg "255" )
instead of J2 but I need the equation linked to the value in J2 which is
again linked to other sheets.

A D
GL Sub Class "Transaction
Ccy Amount"
100 107,299.21
0 .00
0 .00
255 3,368,000.00
0 .00
0 .00
394 -11,210.26
0 .00
0 .00
454 5,486.00

Any help please?