Vlookup Question

flduckhunter73

New Member
Joined
Apr 3, 2013
Messages
4
Hey there,

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

CityVlookupMag NameMag Id
Tallahassee?HomesAndLand.com0088
KelownaHomes & Land of Tallahassee0108
Santa CruzHomes & Land of Ocean County, NJ0116
CalgaryEstates & Homes of Greater New Hampshire & Southern Maine0125

<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!!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,408
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Apr 3, 2013
Messages
4
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

Active Member
Joined
Jul 20, 2012
Messages
325
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Try

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

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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

New Member
Joined
Apr 3, 2013
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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

New Member
Joined
Apr 3, 2013
Messages
4
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,867
Messages
5,598,542
Members
414,245
Latest member
allyciv

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top