Can VLOOKUP return multiple cells found in a single row?

%3CLINGO-SUB%20id%3D%22lingo-sub-3542133%22%20slang%3D%22en-US%22%3ECan%20VLOOKUP%20return%20multiple%20cells%20found%20in%20a%20single%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3542133%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20a%20dashboard%20tab%20where%20I%20have%20a%20an%20input%20cell%20that%20contains%20a%20list%20of%20cells%20from%20the%20data%20validation%20tool.%20This%20is%20my%20source%20of%20lookup%20value.%20It%20looks%20like%20this%3A%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%20image-alt%3D%22JaromV_1-1655974564184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_1-1655974564184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_1-1655974564184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_1-1655974564184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_1-1655974564184.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382953i0CE256F3634E1C4D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JaromV_1-1655974564184.png%22%20alt%3D%22JaromV_1-1655974564184.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20table%20array%20looks%20something%20like%20this%3A%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%20image-alt%3D%22JaromV_0-1655973678646.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_0-1655973678646.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_0-1655973678646.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_0-1655973678646.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22JaromV_0-1655973678646.png%22%20style%3D%22width%3A%20689px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F382950i326F891D319B9204%2Fimage-dimensions%2F689x100%3Fv%3Dv2%22%20width%3D%22689%22%20height%3D%22100%22%20role%3D%22button%22%20title%3D%22JaromV_0-1655973678646.png%22%20alt%3D%22JaromV_0-1655973678646.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20to%20input%20the%20Commercial%20Invoice%20No.%20to%20the%20input%20cell%20mentioned%20above%20and%20the%20result%20would%20display%20the%20corresponding%20information%20for%20all%20columns%20in%20the%20dashboard.%20However%20most%20columns%20have%20multiple%20cells%20that%20are%20included%20in%20my%20search.%20The%20%22SKU%22%20Column%20for%20example%20has%20multiple%20cells%20that%20needs%20to%20be%20shown.%26nbsp%3BI%20know%20merging%20can%20be%20problematic%2C%20but%20I%20do%20not%20know%20how%20else%20to%20organize%20the%20data%20table.%20Tips%20would%20be%20highly%20appreciated!%20TIA!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3542133%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3544322%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20VLOOKUP%20return%20multiple%20cells%20found%20in%20a%20single%20row%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3544322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1433735%22%20target%3D%22_blank%22%3E%40JaromV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20say%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3E%3CU%3EI%20know%20merging%20can%20be%20problematic%3C%2FU%3E%2C%20but%20I%20do%20not%20know%20how%20else%20to%20organize%20the%20data%20table.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EAnd%20with%20that%26nbsp%3B%3C%2FSPAN%3Eacknowledgment%20you've%20put%20your%20finger%20on%20the%20problem.%20%3CSTRONG%3EA%20table%2C%20an%20Excel%20table%2C%20does%20NOT%20work%20with%20merged%20rows%3C%2FSTRONG%3E%20for%20such%20things%20as%20Invoice%20numbers%20or%20LOT%2C%20etc.%20It%20may%20%22look%20nice%22%20to%20the%20human%20eye%2C%20but%20it%20totally%20interferes%20with%20the%20computer's%20abilities--which%20are%20many--to%20parse%20the%20data%20as%20you%20now%20want%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20else%20to%20organize%3F%20Repeat%20the%20Invoice%20number%20and%20Sales%20contract...whatever%20needs%20to%20be%20done.%20Basically%2C%20you%20need%20to%20reorganize%20your%20data%20into%20a%20standard%20Excel%20Table.%20Then%20you'll%20probably%20find%20that%20FILTER%20(a%20new%20relatively%20new%20function%20that%20does%20require%20the%20most%20current%20version%20of%20Excel)%20will%20work%20wonders%20for%20you.%20But%20start%20with%20%26nbsp%3Breorganizing%20your%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHerewith%2C%20some%20references%20for%20you%20to%20research.%3C%2FP%3E%3CP%3EOn%20Tables%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fglossary%2Fexcel-table%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fglossary%2Fexcel-table%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20FILTER%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I made a dashboard tab where I have a an input cell that contains a list of cells from the data validation tool. This is my source of lookup value. It looks like this:

 

JaromV_1-1655974564184.png

 

 

My table array looks something like this:

 

JaromV_0-1655973678646.png

 

I wish to input the Commercial Invoice No. to the input cell mentioned above and the result would display the corresponding information for all columns in the dashboard. However most columns have multiple cells that are included in my search. The "SKU" Column for example has multiple cells that needs to be shown. I know merging can be problematic, but I do not know how else to organize the data table. Tips would be highly appreciated! TIA!

 

1 Reply

@JaromV 

 

You say 

I know merging can be problematic, but I do not know how else to organize the data table.

 

And with that acknowledgment you've put your finger on the problem. A table, an Excel table, does NOT work with merged rows for such things as Invoice numbers or LOT, etc. It may "look nice" to the human eye, but it totally interferes with the computer's abilities--which are many--to parse the data as you now want to do.

 

How else to organize? Repeat the Invoice number and Sales contract...whatever needs to be done. Basically, you need to reorganize your data into a standard Excel Table. Then you'll probably find that FILTER (a new relatively new function that does require the most current version of Excel) will work wonders for you. But start with  reorganizing your data.

 

Herewith, some references for you to research.

On Tables: https://exceljet.net/glossary/excel-table

 

On FILTER: https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...