Vlookup Question

[flduckhunter73]

Hey there,

I am trying to use a vlookup to pull and ID for some company magazines based on a given city.

City	Vlookup	Mag Name	Mag Id
Tallahassee	?	HomesAndLand.com	0088
Kelowna		Homes & Land of Tallahassee	0108
Santa Cruz		Homes & Land of Ocean County, NJ	0116
Calgary		Estates & Homes of Greater New Hampshire & Southern Maine	0125

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I tried using a VLookup =VLOOKUP(A2,C:D,2,TRUE)

The problem I am running into is that the vlookup is searching the arrays and pulling a magID, but it is pulling a magID that does not match the string, and it is pulling the same mag ID every time...

Do I need some sort of nested function to properly match the partial string?

I wasnt to look at the city (Tallahassee) check the mag name column to see if it contains tallahassee, and then if it does retrieve the mag ID for the match.

Please help!!

 

[etaf]

this TRUE at the end will be the problem - that takes the closest match
so you need to use false to get an exact match
=VLOOKUP(A2,C:D,2,FALSE)

 

[flduckhunter73]

this TRUE at the end will be the problem - that takes the closest match
so you need to use false to get an exact match
=VLOOKUP(A2,C:D,2,FALSE)

I have tried that and I am received the #N/A error. If I am not mistaken this makes the column check for strings matching "Tallahassee" exactly, which would not allow Homes & Land of Tallahassee return a match.

 

[jai9]

Hi,

Assuming that you are looking at the city Tallahassee and trying to get the Mag ID which is 0088, Try

=VLOOKUP(A2,A:D,4,FALSE)

Jai

 

[Jonmo1]

Try

=VLOOKUP("*"&A2,C:D,2,FALSE)

 

[Weazel]

I wasnt to look at the city (Tallahassee) check the mag name column to see if it contains tallahassee, and then if it does retrieve the mag ID for the match.
Please help!!

maybe something along the lines of....

=INDEX($D$2:$D$5,MATCH(TRUE,ISNUMBER(SEARCH(A2,$C$2:$C$5)),0)) CTRL+SHIFT+ENTER

 

[flduckhunter73]

maybe something along the lines of....

=INDEX($D$2:$D$5,MATCH(TRUE,ISNUMBER(SEARCH(A2,$C$2:$C$5)),0)) CTRL+SHIFT+ENTER

I am not too sure how this function works, but it is still giving me a #N/A error.

 

[Weazel]

make sure you commit the formula with CONTROL SHIFT ENTER not just ENTER because it is an array.

essentially the index is returning the Mag ID, the Match is searching C2:C5 for what is in A2, ideally 'tallahassee', then, if you drag it down the formula will increment to search for Kelowna then Santa Cruz, and so on...
The Search returns a number and the isnumber returns a true when a number is found, so the Match looks for the TRUE, when it finds it, it returns it to the the index, which returns the Mag ID to the cell.

Essentially

 

[flduckhunter73]

maybe something along the lines of....

=INDEX($D$2:$D$5,MATCH(TRUE,ISNUMBER(SEARCH(A2,$C$2:$C$5)),0)) CTRL+SHIFT+ENTER

This actually did work. I did not realize that I had to CTRL **** ENTER when executing the formula it worked when I removed that from the formula manually did it. Thanks a bunch!!!

 

[Weazel]

You're very welcome