# Helping solving Vlookup, Match/Index failure

#### hellfire45

##### Active Member
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!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### ExcelMercy

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

#### Michael M

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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

##### MrExcel MVP
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

##### Active Member
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

##### Well-known Member
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

##### Active Member
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

##### Well-known Member
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 !

Replies
6
Views
294
Replies
7
Views
2K
Replies
4
Views
228
Replies
0
Views
582
Replies
0
Views
267

1,190,897
Messages
5,983,447
Members
439,843
Latest member
PlanetFitness

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back