{"id":200,"date":"2009-11-25T23:33:00","date_gmt":"2009-11-25T23:33:00","guid":{"rendered":"http:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/"},"modified":"2009-11-25T23:33:00","modified_gmt":"2009-11-25T23:33:00","slug":"working-on-excel","status":"publish","type":"post","link":"http:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/","title":{"rendered":"Working on Excel"},"content":{"rendered":"

I have been working on Excel during these days and I have the problem that I didn’t know how to return multiple values from a lookup: some formula like VLookup but that it returns more than one value.<\/p>\n

I have found these very useful links<\/p>\n

http:\/\/office.microsoft.com\/en-gb\/excel\/HA012260381033.aspx<\/a><\/p>\n

http:\/\/www.mrexcel.com\/articles\/CSE-array-formulas-excel.php<\/a><\/p>\n

I have tried to resolve my problem using the help explained in the first link but it does not work as I need.<\/p>\n

I have finally created a macro to build the incoming array I need.<\/p>\n

The next problem I’m facing seems to be something without solution, because I cannot use the references in a “list” of the “Data validation” wizard:<\/p>\n

\"\" <\/p>\n

One day later….<\/em><\/p>\n

I was tired last night. To overcome the problem I pasted the formula in a cell and then I got the reference from that field. I hide the cell and everything works as desired.<\/p>\n","protected":false},"excerpt":{"rendered":"

I have been working on Excel during these days and I have the problem that I didn’t know how to return multiple values from a lookup: some formula like VLookup but that it returns more than one value. I have found these very useful links http:\/\/office.microsoft.com\/en-gb\/excel\/HA012260381033.aspx http:\/\/www.mrexcel.com\/articles\/CSE-array-formulas-excel.php I have tried to resolve my problem using … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[],"class_list":["post-200","post","type-post","status-publish","format-standard","hentry","category-tool"],"yoast_head":"\nWorking on Excel -<\/title>\n<meta name=\"description\" content=\"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Working on Excel -\" \/>\n<meta property=\"og:description\" content=\"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects\" \/>\n<meta property=\"og:url\" content=\"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"joapen projects\" \/>\n<meta property=\"article:published_time\" content=\"2009-11-25T23:33:00+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG\" \/>\n<meta name=\"author\" content=\"joapen\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"joapen\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/\"},\"author\":{\"name\":\"joapen\",\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#\\\/schema\\\/person\\\/23919df2312175fe9c4609203595b217\"},\"headline\":\"Working on Excel\",\"datePublished\":\"2009-11-25T23:33:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/\"},\"wordCount\":163,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#\\\/schema\\\/person\\\/23919df2312175fe9c4609203595b217\"},\"image\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/1.bp.blogspot.com\\\/_FHKXh6Fis30\\\/Sw2_7U9Y0rI\\\/AAAAAAAABJ4\\\/J6hoICqv27I\\\/s320\\\/data.validation.Excel.JPG\",\"articleSection\":[\"Tool\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/\",\"url\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/\",\"name\":\"Working on Excel -\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/1.bp.blogspot.com\\\/_FHKXh6Fis30\\\/Sw2_7U9Y0rI\\\/AAAAAAAABJ4\\\/J6hoICqv27I\\\/s320\\\/data.validation.Excel.JPG\",\"datePublished\":\"2009-11-25T23:33:00+00:00\",\"description\":\"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#primaryimage\",\"url\":\"http:\\\/\\\/1.bp.blogspot.com\\\/_FHKXh6Fis30\\\/Sw2_7U9Y0rI\\\/AAAAAAAABJ4\\\/J6hoICqv27I\\\/s320\\\/data.validation.Excel.JPG\",\"contentUrl\":\"http:\\\/\\\/1.bp.blogspot.com\\\/_FHKXh6Fis30\\\/Sw2_7U9Y0rI\\\/AAAAAAAABJ4\\\/J6hoICqv27I\\\/s320\\\/data.validation.Excel.JPG\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/2009\\\/11\\\/25\\\/working-on-excel\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/joapen.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Working on Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#website\",\"url\":\"http:\\\/\\\/joapen.com\\\/blog\\\/\",\"name\":\"joapen projects\",\"description\":\"Just a place to write\",\"publisher\":{\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#\\\/schema\\\/person\\\/23919df2312175fe9c4609203595b217\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/joapen.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\\\/\\\/joapen.com\\\/blog\\\/#\\\/schema\\\/person\\\/23919df2312175fe9c4609203595b217\",\"name\":\"joapen\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/joapen-mini.jpeg\",\"url\":\"https:\\\/\\\/joapen.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/joapen-mini.jpeg\",\"contentUrl\":\"https:\\\/\\\/joapen.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/joapen-mini.jpeg\",\"width\":400,\"height\":400,\"caption\":\"joapen\"},\"logo\":{\"@id\":\"https:\\\/\\\/joapen.com\\\/blog\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/joapen-mini.jpeg\"},\"sameAs\":[\"http:\\\/\\\/www.joapen.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Working on Excel -","description":"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/","og_locale":"en_US","og_type":"article","og_title":"Working on Excel -","og_description":"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects","og_url":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/","og_site_name":"joapen projects","article_published_time":"2009-11-25T23:33:00+00:00","og_image":[{"url":"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG","type":"","width":"","height":""}],"author":"joapen","twitter_misc":{"Written by":"joapen","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#article","isPartOf":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/"},"author":{"name":"joapen","@id":"http:\/\/joapen.com\/blog\/#\/schema\/person\/23919df2312175fe9c4609203595b217"},"headline":"Working on Excel","datePublished":"2009-11-25T23:33:00+00:00","mainEntityOfPage":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/"},"wordCount":163,"commentCount":0,"publisher":{"@id":"http:\/\/joapen.com\/blog\/#\/schema\/person\/23919df2312175fe9c4609203595b217"},"image":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#primaryimage"},"thumbnailUrl":"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG","articleSection":["Tool"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/","url":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/","name":"Working on Excel -","isPartOf":{"@id":"http:\/\/joapen.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#primaryimage"},"image":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#primaryimage"},"thumbnailUrl":"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG","datePublished":"2009-11-25T23:33:00+00:00","description":"I have been working on Excel during these days and I have the problem that I didn't know how to return multiple values from a lookup: some formula like - joapen projects","breadcrumb":{"@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#primaryimage","url":"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG","contentUrl":"http:\/\/1.bp.blogspot.com\/_FHKXh6Fis30\/Sw2_7U9Y0rI\/AAAAAAAABJ4\/J6hoICqv27I\/s320\/data.validation.Excel.JPG"},{"@type":"BreadcrumbList","@id":"https:\/\/joapen.com\/blog\/2009\/11\/25\/working-on-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/joapen.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Working on Excel"}]},{"@type":"WebSite","@id":"http:\/\/joapen.com\/blog\/#website","url":"http:\/\/joapen.com\/blog\/","name":"joapen projects","description":"Just a place to write","publisher":{"@id":"http:\/\/joapen.com\/blog\/#\/schema\/person\/23919df2312175fe9c4609203595b217"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/joapen.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/joapen.com\/blog\/#\/schema\/person\/23919df2312175fe9c4609203595b217","name":"joapen","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/joapen.com\/blog\/wp-content\/uploads\/2021\/04\/joapen-mini.jpeg","url":"https:\/\/joapen.com\/blog\/wp-content\/uploads\/2021\/04\/joapen-mini.jpeg","contentUrl":"https:\/\/joapen.com\/blog\/wp-content\/uploads\/2021\/04\/joapen-mini.jpeg","width":400,"height":400,"caption":"joapen"},"logo":{"@id":"https:\/\/joapen.com\/blog\/wp-content\/uploads\/2021\/04\/joapen-mini.jpeg"},"sameAs":["http:\/\/www.joapen.com"]}]}},"_links":{"self":[{"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/posts\/200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/comments?post=200"}],"version-history":[{"count":0,"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/posts\/200\/revisions"}],"wp:attachment":[{"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/media?parent=200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/categories?post=200"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/joapen.com\/blog\/wp-json\/wp\/v2\/tags?post=200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}