Vlookup confusion


June 15th, 2013 by Ben Utley Ben Utley

I will try to simplify this as much as possible. I have been using vlookup, but I don’t know how to make it work with this. Lets say my data has column A which is a country and column B which is its officials. Now with a vlookup if I want to find one official thats easy because it is in the same row as the country, but if I want multiple officials to pop up when I search the country’s name, (like A1: America, B1: Obama, B2: Biden in my data) how do i get it to bring up both Obama and Biden’s name without having to type America in A2 as well.

http://www.excel-course.com/wp-content/uploads/2013/06/basicsearch.xlsx

3 Responses to “Vlookup confusion”

  1. admin admin Says:

    It would be easier to understand if you upload a sample file to explain.

  2. BenneTan BenneTan Says:

    hi there ben. as the admin advised, it’s better to send us a sample excel file. you can send it to:
    benne.tan@everydayexcel.com.sg

    do you need Obama & Biden both in the same cell? if you’re not sure how many instances of such will appear, you might need VBA. if you don’t mind it appearing in different rows or columns, then it’s possible with formulas.

  3. BenneTan BenneTan Says:

    i have done up 2 versions for you. it would be ideal if the country do not have merged cells & every row is filled up. I did that in Sheet2 & the solution would be much simpler.

    If that’s impossible, then i also did another version in Sheet1. do note that both formulas are array formulas. that means you have to press CTRL + SHIFT + ENTER to confirm the formula. when done correctly, you should see curly brackets surrounding the formula.

    http://www.excel-course.com/wp-content/uploads/2013/06/20130618-basicsearch.xlsx

Leave a Reply

You must be logged in to post a comment.

s2Member®