(Should be) Simple vlookup to external workbook

**benolsen** · 02-10-2012, 03:13 PM

For some reason, I'm having trouble with this simple vlookup formula.

I am a realtor and have a "Grand List" which is folder full of excel workbooks that have people updated addresses. There are about 30 individual excel documents altogether and I would like to be able to search for a persons last name and have the results populate. Here is what I've done:

I created a separate excel file titled "MASTER_SEARCH" and save it in the folder with all other workbooks. From there I created a field in MASTER_SEARCH where I can input a last name and now, where I'm stuck, is in creating the vlookup that searches for the name I input in the 30 different documents.

The forumula I'm using is =IFERROR(VLOOKUP($B$4,NameOfTown.xls!A2:AN654,3,TRUE),"Value Not Found") and nothing I can do will return a value. I'm using an IFERROR because I would need to purge all 30 workbooks. I've double checked the names on the files and even if I move the values into another sheet the same workbook I cannot get a value to return. I'm absolutely befuddled.

**NBVC** · 02-10-2012, 03:23 PM

Does this help: http://www.xl-central.com/lookup-sin...le-sheets.html

**benolsen** · 02-10-2012, 03:59 PM

That sort of helps but the issue seems to be with the vlookup part of the formula, which I appear to have correct.

I've attached my workbook so you're seeing what I'm seeing...

**NBVC** · 02-10-2012, 04:04 PM

The lookup table argument should probably start at column G, not A...

Sheet2!G2:AN653

Then count columns from G to get column number to extract data from within that range...

Also if there is not an exact match and the table is not sorted, you will get false results... you might want to consider using FALSE instead of TRUE if that possibility is real, for the last argument.

**benolsen** · 02-13-2012, 09:24 AM

That worked! I didn't think that would cause an issue because the search range included column G...

As for the second part you pointed out; I was thinking that I could simply insert this code multiple times thinking it wouldn't return the same values if they had already been provided but that was really short-sighted. Again, what I would like to accomplish is the ability to type in "Allen" for example and get all results, whether "Allen" was in their first or last name. I am now seeing this formula could be significantly more involved than I had thought. Is there even a way to create something like this?

**NBVC** · 02-13-2012, 09:31 AM

So as long as column G Sheet2 contains whatever is typed into B4 of Sheet1, then list addresses from column I of Sheet2, and likewise if anything in column H contains the item in B4, then list the column J addresses? Is that right?

**benolsen** · 02-13-2012, 10:48 AM

You got it exactly. The only addendum is that what is typed into B4 on Sheet1 will more often than not loosely match what is from column H or I (i.e. B4 = "Allen", column H,I = "Allen George & Beth".

**NBVC** · 02-13-2012, 01:29 PM

In B6, try:

=IFERROR(INDEX(Sheet2!$I$2:$I$653,SMALL(IF(ISNUMBER(SEARCH($B$4,Sheet2!$G$2:$G$653)),ROW(Sheet2!$G$2:$G$653)-ROW(Sheet2!$G$2)+1),ROWS($B$6:$B6))),IFERROR(INDEX(Sheet2!$J$2:$J$653,SMALL(IF(ISNUMBER(SEARCH($B$4,Sheet2!$H$2:$H$653)),ROW(Sheet2!$H$2:$H$653)-ROW(Sheet2!$H$2)+1),ROWS($B$6:$B6)-COUNTIF(Sheet2!$G$2:$G$653,"*"&$B$4&"*"))),""))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down as far as you need.

This will search sheet2 that contains the word or sequence of words in B4 (exact match).

Note: If there is a match, but no address, then a 0 will be returned. You can format the column as 0;-0;;@ to hide 0's.

Also, I deleted your attachment as it looks like it may be containing confidential information. Please make sure to scrub attachments.