Require formula to identify a number, not text

I have the following formula which currently allows me to overcome the maximum 7 if formula limitation, however I have it looking at text and I wish to use the formula to look up a numerical value.

Formula in cell AF10 is: If(AE10<>"",INDEX(R10:AD10,MATCH(AE10,R7:AD7,0)),"")

AE10 represents the minimum price in the range from R10:AD10 with R7:AD7 representing the name of the companies submitted price.

I am trying to get the name of the company that matches the cheapest price to come up in cell AF10, however all I get is #NA.

Appreciate any assistance

FBS

Hi Fbs1960

I think that you have to modify your formula. Something like this maybe?

=IF(AE10<>"";INDEX(R7:AD7;MATCH(AE10;R10:AD10;0)))

Hope to helps you.

you need to look at what you are doing as fotis1991 shows
the match part finds the row the info is in, so match min price ae10 against range of prices R10:AD10
MATCH(AE10,R10:AD10,0) {warning if there are 2 or more = prices it will only find the first one}
the result of this is a position number in that range which is then used to retrieve info from the index
your index in this case is R7:AD7 which is only one row high
INDEX(R7:AD7,"position number found by match") so if a10 was matched in s10 the result would be 2
position 2 of index R7:AD7 is s7 and that is the result you'd get back for a more detailed explanation read
here http://www.contextures.com/xlfunctions03.html

Hi fbs,

See the attached file where I have tried solving your issue with two options, choose one which you feel ok.
In formula, Zero at the end is changed to 1 for nearest match of the price for different companies.

Regards,
DILIPandey

<click on below 'star' if this helps>