VLOOKUP returning "Value Not Available" - tests run, still occuring

So as the title suggests I have been having some problems with Vlookup, i stumbled across this thread first

https://www.excelforum.com/excel-gen...n-vlookup.html

and ran the tests that way - sadly it's still returning this error and ill be damned if i can figure out what's causing it. To sum up what's been done:

1) Copied values from original sheet to new sheet as text
2) Re-written the numbers manually
3) Performed cell value length, isnumber function - proved true and a match respectively.
4) Performed IF function to test if the numbers are exact, returned true value (Sheet1;H12)
5) Performed INDEX/MATCH function on arrays, returned value not available error

I have attached the spreadsheet to suit. I have this nagging feeling i've made a rookie error. but if anyone could help it would be very much appreciated.

Junker

spreadsheet didnt attach again.

Hi MrShorty,

Thanks for your reply, you're correct about the observations. Honestly i was running the MATCH() function to see if it would return a different value to the VLOOKUP attempt as the INDEX/MATCH combo should have at least returned something. I ran a sample "-1", "0" and "+1" (Row 1,2 & 3) to see if it would perform any differently but after trying with the array change as you suggested but still no luck.

Not sure as to the relevance but the core data was generated via SQL script/output. For reference this report came from a different database to what i am used to (same core program but the latest version) so I have been trying to see if there was a difference between the current vs. this one.

INDEX(Sheet1!C6:C112,MATCH(Sheet2!C6,3,0)) which returned #N/A and

INDEX(Sheet1!C:C,MATCH(Sheet2!C2,Sheet1!B2:B108,0)) [after your reply] which returned "19" so i have screwed up something stupid. Ill admit my knowledge of INDEX/MATCH functions is limited so i'm trying learn on the fly.

If i may, what on earth could have caused the recurring error for the VLOOKUP? Far as i can tell it was all pretty standard, but maybe there is a slight formatting error of some kind?

I understand now, the first one i mistakenly was interpreting as a column position in the overall array (as if it were VLOOKUP). And with the secondary error, and you're right it worked so THANK YOU SO MUCH!!!

As far as the reasoning why, this was part of a database migration from similar systems and the output from the database will be used in conjunction with an ancillary programme for reporting. So if there was something i could at least fix, or diagnose then it will save me a lot of headaches down the track.