Forum Discussion

Thetest123's avatar
Thetest123
Copper Contributor
Apr 13, 2019

VLOOKUP Help

Hi everyone,   Can someone please help me with using the VLOOKUP function. Attached below is a chart I am working with. Essentially I'm trying to search by location and return the corresponding nam...
excel
Excel Online
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 13, 2019

Thetest123 , VLOOKUP only looks to the right, you can't retrieve data from the column on the left. Use INDEX/MATCH instead, like

=IFNA(INDEX(A2:A10, MATCH("Dallas",B2:B10,0)),"no such")

 

  • Thetest123's avatar
    Thetest123
    Copper Contributor
    Apr 13, 2019

    SergeiBaklan 

    Oh I see! That makes sense. What about when there are duplicates of location like for Spain? Is there a way to use the index match to return all the corresponding names and not just the first one. When I use

    =IFNA(INDEX(A2:A10, MATCH("Spain",B2:B10,0)),"no such")

    it only returns Dawn...is there a way for it to return Morgan and John as well?

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 13, 2019

      Thetest123 , nope, INDEX/MATCH, as well as VLOOKUP, returns only first found value. You may only play with options how to find it. If the list of matched records that will be another technique.

      And it depends on how you'd like to see the result on which Excel you are. If, for example, list of values for given criteria and you are Office365 subscriber you may use TEXTJOIN as