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

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0

Forum statistics

Threads
1,218,744
Messages
6,144,229
Members
450,531
Latest member
avril18

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
Back
Top