Vlookup Part of Cell

**VegasL** · 12-22-2009, 04:02 PM

Hello,

I am trying to compare two sets of data on two different tabs.

Comparing tabs, GA vs Puburl

On tab puburl, the list contains data such as:

http://www.abcdef.com/dasfsdf/index.html
http://www.alllittirs.com

I want to compare this against the data in tab Ga, which contains data such as:

Source Numbers Euros
alllittirs.com 23973 454
defghi.com 7725 212

The end result should be in tab puburl:, to display the numbers and euros such as in example of spreadsheet attached.

If it doesn't find the matching result to display blank.

Basically, if the cell contains http://www. or anything preceding site.com, it should match.

I tried, =IF(ISNA(VLOOKUP("*"&GA!A2&"*",PUBURL!$A$2:$C$6,2,FALSE)),"",VLOOKUP("*"&GA!A2&"*",PUBURL!$A$2:$C$6,2,FALSE)) but that didnt give desired result, in pub url tab for cell b2 and copied down.

**NBVC** · 12-22-2009, 04:13 PM

Try:

=IF(ISNA(LOOKUP(2,1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10)),"",LOOKUP(2,1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10))

copied down and over to next column

adjust ranges to suit.

**VegasL** · 12-30-2009, 06:05 PM

Thanks NBVC. Works Perfectly. Though I'm having a hard time understanding it. Here's how I'm interpreting it:

1. (SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10)) It searches for GA!$A$2:$A$10,$A2, within $A2

2. If Then it performs 1/ISNUMBER Function, Checking for True/False. If it does not find (false) it equates to (1/False), but the isnumber function "cancels" the Div 0 error out

3. It the Result from 1/ISNUMBER Function is True, it then Looksup on GA!B$2:B$10

4. And Then Does Lookup value (2). ---- I have no idea what the Lookup(2) does, why did you do this?

5. Finally it performs ISNA Function. If everything inside (LOOKUP(2,1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10) is False, it puts "", else it performs (LOOKUP(2,1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10)

is this the correct interpretation?

Finally, is there a way to do something similar to =IF(ISNA(VLOOKUP("*"&GA!A2&"*",PUBURL!$A$2:$C$6,2,FALSE)),"",VLOOKUP("*"&GA!A2&"*",PUBURL!$A$2:$C$6, 2,FALSE)), basically using a simple vlookup with * ?

**VegasL** · 01-05-2010, 07:20 PM

bumping up, anyone?

**NBVC** · 01-05-2010, 10:05 PM

SEARCH(GA!$A$2:$A$10,$A2) searches A2 for any of the text strings within GA!$A$2:$A$10

ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)) returns TRUE for all items in GA!$A$2:$A$10 found in $A2 and FALSE for those not found... it returns an array...

1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)) returns 1's and #DIV/0 errors based on result of 1/TRUE or 1/FALSE results in the array (where TRUE is equivalent to 1 and FALSE is equivalent to 0).

LOOKUP(2,1/ISNUMBER(SEARCH(GA!$A$2:$A$10,$A2)),GA!B$2:B$10) looks for a 2 in the array of 1's and #DIV/0 errors and finds the last time the item is smaller than or equal to 2.. ignoring #DIV/0 errors... which in your case should be the last time and only time.. since there is one match... and then it returns the corresponding item in GA!B$2:B$10.

The IF(ISNA()) part looks to see if no results match.. i.e. if the LOOKUP() formula finds no matches, then #N/A is returned... if that is the case, then a null is returned, if not, then it re-evaluates the LOOKUP() function and returns a result.

Review each function in EXCEL help files for more details on the basic functionality of each.