Index - Match - ROWS formula not working?

Hi
i tried to do an index(array(match) formula, however the result always gives me an error (#NA)

here's the formula i entered:

=INDEX(Table!$A$1:$D$9,MATCH(C1,Table!$A$1:$A$9,0),MATCH(B1,Table!$A$1:$D$1,0))

the column match formula works fine, however the row match formula does not

i have attached the file i was working on, and the errors are highlighted in yellow

can i please get some guidance on this? have formatted the figures to numbers & text (and vice versa) but it still will not work...

thanks in advance

Marvin

Hi
thanks for the replies guys.. it seems like the issue is the formatting indeed

i tried to reformat the original sheet where i got the data from, but it did not work as i expected as the figures are derived from formulas. tried to change the format cell of these formula to "numbers with 2 decimal places", and the formatting did not change the figures?

what did i do wrong here? here's the new attached files with the formulas which i attempted to do a cell reformat. if you could tell me how to format the yellow items for the formula to work that would be great!!

thanks

Marvin

Your formulas in column F are returning a text string.

=IF(ISNUMBER(SEARCH("19",B2)),"19".....

You are returning the string 19, not the number 19. Formatting the cell to be a number is ignored if the content is text.

Select column F, then do a find & replace to change all double quotes to the empty string. The SEARCH will still work because Excel will take the number and automatically convert it to text to do the search.

Are the strings in column B a consistent format? That is, do they always start with a "$" and a number? If so, you could use this simpler formula in column F:

=MID(B1,2,FIND(" ",B1)-2)+0

All good Sir!!! thanks to the both of you! !Rep!!