Home

VLOOKUP Help

%3CLINGO-SUB%20id%3D%22lingo-sub-449614%22%20slang%3D%22en-US%22%3EVLOOKUP%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449614%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%20me%20with%20using%20the%20VLOOKUP%20function.%20Attached%20below%20is%20a%20chart%20I%20am%20working%20with.%20Essentially%20I'm%20trying%20to%20search%20by%20location%20and%20return%20the%20corresponding%20name%20associated%20with%20it.%20However%2C%20when%20I%20use%20the%20formula%3A%20%3DVLOOKUP(%22Dallas%22%2CA2%3AB10%2C1%2CFALSE)%2C%20instead%20of%20returning%20Jones%2C%20it%20returns%20%23N%2FA.%20In%20addition%2C%20%3DVLOOKUP(%22Spain%22%2CA2%3AB10%2C1%2CFALSE)%2C%20instead%20of%20returning%20Dawn%2C%20Morgan%2C%20and%20John%2C%20it%20returns%20%23N%2FA.%20Can%20someone%20please%20help%20me%20this%3F%20I'm%20on%20a%20deadline%20and%20really%20frustrated%20with%20trying%20to%20figure%20this%20out!%20Thank%20you%20so%20much!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20208px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108588iFC6734B791B984CB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Example.jpg%22%20title%3D%22Example.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-449614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449644%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320316%22%20target%3D%22_blank%22%3E%40Thetest123%3C%2FA%3E%26nbsp%3B%2C%20VLOOKUP%20only%20looks%20to%20the%20right%2C%20you%20can't%20retrieve%20data%20from%20the%20column%20on%20the%20left.%20Use%20INDEX%2FMATCH%20instead%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFNA(INDEX(A2%3AA10%2C%20MATCH(%22Dallas%22%2CB2%3AB10%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449696%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449696%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOh%20I%20see!%20That%20makes%20sense.%20What%20about%20when%20there%20are%20duplicates%20of%20location%20like%20for%20Spain%3F%20Is%20there%20a%20way%20to%20use%20the%20index%20match%20to%20return%20all%20the%20corresponding%20names%20and%20not%20just%20the%20first%20one.%20When%20I%20use%3C%2FP%3E%3CPRE%3E%3DIFNA(INDEX(A2%3AA10%2C%20MATCH(%22Spain%22%2CB2%3AB10%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%3CP%3Eit%20only%20returns%20Dawn...is%20there%20a%20way%20for%20it%20to%20return%20Morgan%20and%20John%20as%20well%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449749%22%20slang%3D%22en-US%22%3ERe%3A%20VLOOKUP%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320316%22%20target%3D%22_blank%22%3E%40Thetest123%3C%2FA%3E%26nbsp%3B%2C%20nope%2C%20INDEX%2FMATCH%2C%20as%20well%20as%20VLOOKUP%2C%20returns%20only%20first%20found%20value.%20You%20may%20only%20play%20with%20options%20how%20to%20find%20it.%20If%20the%20list%20of%20matched%20records%20that%20will%20be%20another%20technique.%3C%2FP%3E%0A%3CP%3EAnd%20it%20depends%20on%20how%20you'd%20like%20to%20see%20the%20result%20on%20which%20Excel%20you%20are.%20If%2C%20for%20example%2C%20list%20of%20values%20for%20given%20criteria%20and%20you%20are%20Office365%20subscriber%20you%20may%20use%20TEXTJOIN%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20684px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108606iF35426B4534062B3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Thetest123
New Contributor

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 name associated with it. However, when I use the formula: =VLOOKUP("Dallas",A2:B10,1,FALSE), instead of returning Jones, it returns #N/A. In addition, =VLOOKUP("Spain",A2:B10,1,FALSE), instead of returning Dawn, Morgan, and John, it returns #N/A. Can someone please help me this? I'm on a deadline and really frustrated with trying to figure this out! Thank you so much!!!

 

 

Example.jpg

3 Replies

@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")

 

Highlighted

@Sergei Baklan 

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?

@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

image.png

 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies