Helping solving Vlookup, Match/Index failure

[hellfire45]

Would anybody be willing to take a look at an excel file I've got that has a VLookup and Match/index returning both #N/A? I've tried searching on the web for solutions but everything I've found so far as failed.

I can email the file out. I'm sure for somebody who is more skilled this is an easy fix but I seem to always have continuous problems with these formulae.

Thank you!

 

[ExcelMercy]

Can you post your formula? A lot of times I'll use IfError( instead of VLookUp

 

[Michael M]

Hellfire
A lot of posters will not risk opening an unknown source !
If you want to post the workbook use an upload site like Mediafire or Dropbox, then post the link back here.
Having said that, it might be easier to simply use the HTML Maker in my tag and use it to post a SMALL sample of data back here !!!

 

[hellfire45]

I'm trying both of these. They're both returning #N/A. I've tried switching from exact to approximate.

=VLOOKUP(F2,$J$1:$K$17,2,FALSE)
=INDEX($J$1:$K$17,MATCH(F2,$J$1:$J$17,0),2)

 

[Michael M]

Can you post a small sample of reference data ??
Is it a text string you are looking up ?
What's in F2 as opposed to the table J1:K17 ?

 

[AlphaFrog]

As a test, put a formula like this in an empty cell
=F2=J10

Where J10 is the expected match for F2. If they truly match, the formula will return TRUE. If you get a FALSE, they do not match for some reason.

What is in F2 and column J; Numbers, Text?

 

[hellfire45]

Okay, I made a MediaFire account and uploaded the file:
Draft Kings Scatter Plot

Hopefully that will work. I hope one of you can figure it out before I go nuts! I highlighted the formulae that are having problems in yellow. Originally the index array way on a seperate sheet but I moved it onto the same sheet thinking it might fix the problem.

 

[Michael M]

Sorry Hellfire, I can't access External sites at the moment..
Hopefully one of the other guys will be able to have a look !
Did you try AlphaFrogs test on your data ??

 

[hellfire45]

I tried the test and as I suspected, it says "FALSE". Both cells have the same text in them and they are both set as "General" format above. This is a list of quarterbacks in the NFL. So the IndexMatch formula is attempting to match a quarterbacks name with his anticipated value. Column F has their name, and Columns J:K is th indexed table containing name and value (e.g. Peyton Manning 7800).

 

[Michael M]

If you are getting a FALSE then the texts aren't exactly the same !
HAve a look at either / both for possible leading / trailling spaces or double spaces in the text.
Try another formula
=Len(F2)
and =Len(J10)
this will tell you how many charatcers are in each cell....they MUST be the same !