Index, Match not working. Can't even get Vlookup to work. Probably ID10T error.

[Alias1431]

Here are the two files. I am trying to get pricing data in the lower M column in the MILNE tab in the T52 file from the Pricing file. Everything keeps coming back #N/A.

http://tempsend.com/49CC836BB3
http://tempsend.com/1CD4B311B6

=INDEX([Pricing.xlsx]Sheet1!$L$1:$L$471, MATCH(C285, [Pricing.xlsx]Sheet1!$E$1:$E$471, 0))

[etaf]

but the number you are looking up in
28053 for example
does not exist in
[Pricing.xlsx]Sheet1!$E$1:$E$471

most are #value because of the formula looking up a blank

so where it does not find a match it returns #N/A

[Paul]

Hi Alias,

The "values" in column E of your Pricing workbook are text, not numeric. (Using functions like RIGHT, MID, etc. convert numbers to text.)

The "values" in column C of your T52 workbook are numeric. So you're trying to match a number with text. Won't work. To fix you could change the formulas in column E of the Pricing workbook from

=RIGHT(C1,LEN(C1)-1)

to

=RIGHT(C1,LEN(C1)-1)+0

Adding zero to the formula will coerce the text value to numeric, and your INDEX/MATCH will work properly. You could instead convert the values in column C of the T52 workbook to TEXT format, but you'd have to update each cell (F2, Enter).

[Alias1431]

Thanks! That solved it. Didn't even have to F2 anything.