INDEX MATCH #N/A error - number stored as text

[MonkeyFace28]

Hey all,

Firstly, thanks for taking the time to read my query.

I have the below formula, I am getting an #N/A error despite the data being present. I think I have narrowed the error down to the cells and the fact there are text and numbers in the same cell stored as text.*

formula looks like this = =(INDEX('Aged Analysis of Debtors'!$L3,MATCH($A3,'Aged Analysis of Debtors'!$A$3:$K$401,0)))

The reason for this being that it's invoice numbers that "stupidly" start with a "0" so they have them written in excel as "INV 0518561" this is my look up value though...

My question is - does anyone know how to get around this - I've had a quick search but couldn't find anything for keeping the cell as it is now.

thanks peeps

Steph x

* - p.s I know I can put an IFERROR at the front but then I wouldn't get my data

 

[gaz_chops]

Is the Index range a mistake? Just L3!
Also the match range?

 

[KojakWeb]

I'm assuming that A3 refers to the invoice number, e.g. "INV 0518561" in your current worksheet? Where would this appear in your 'Aged Analysis of Debtors' worksheet? If it's in column A, and starts from row 3 and finished at row 401, your formula should read):

=INDEX('Aged Analysis of Debtors'!$L3,MATCH($A3,'Aged Analysis of Debtors'!$A$3:$A$401,0))

 

[MonkeyFace28]

Is the Index range a mistake? Just L3!
Also the match range?

I'm assuming that A3 refers to the invoice number, e.g. "INV 0518561" in your current worksheet? Where would this appear in your 'Aged Analysis of Debtors' worksheet? If it's in column A, and starts from row 3 and finished at row 401, your formula should read):

=INDEX('Aged Analysis of Debtors'!$L3,MATCH($A3,'Aged Analysis of Debtors'!$A$3:$A$401,0))

Thanks for your replies guys. I have just been told by the boss that this sheet isn't needed now as they can set up a report straight from the system... I don't know, you try to help someone out and they throw it back in your face

Thanks again for trying to help xx