Help with a Vlookup pulling from another file

**missmea2005** · 11-14-2012, 09:55 AM

I have two files. I need column N on my CRMsheet to pull data from 107sheet based on criteria in Column A on CRMSheet being the same as column C on 107sheet on the data tab.

**missmea2005** · 11-14-2012, 09:56 AM

I have the following forumula now

HTML Code:

**missmea2005** · 11-14-2012, 10:09 AM

Also tried this

Please Login or Register  to view this content.

I need it to search the entire column, but I am getting nothing

**Ace_XL** · 11-14-2012, 10:14 AM

the lookup value needs to be a single cell. hence

=IFERROR(VLOOKUP($A2,[107Sheet.xlsx]Data!$A$2:$H$171,2,FALSE),"")

Copy down

**missmea2005** · 11-14-2012, 10:19 AM

I'm still getting nothing back. Am I using the incorrect criteria in the forumla?

**Ace_XL** · 11-14-2012, 10:29 AM

Had a look at your files ..

Use
=IFERROR(VLOOKUP($A2,[107Sheet.xlsx]Data!$C:$H,2,FALSE),"")

**FDibbins** · 11-14-2012, 10:37 AM

Thats because the very 1st column of the range you are searching in, HAS to contain what you are searching for, vlookup cannot (easily) search "behind" itself - ie to the left of the left of the column containing the search criteria

For this, you need to use index/match. If you are looking for the grand total, try something like this...

=INDEX('[107Sheet (1).xlsx]Data'!$A$2:$H$171,MATCH(A2,'[107Sheet (1).xlsx]Data'!$C$2:$C$171,0),1)
or...

you could do away with you're data sheet altogether and base everything off the PT on sheet1?
=INDEX('[107Sheet (1).xlsx]Sheet1'!$A$2:$H$171,MATCH(A2,'[107Sheet (1).xlsx]Sheet1'!$B$2:$B$171,0),8)