{"id":451,"date":"2025-10-25T12:07:55","date_gmt":"2025-10-25T10:07:55","guid":{"rendered":"https:\/\/www.silverice.it\/IpercubeTechWP1\/?page_id=451"},"modified":"2025-10-25T12:07:55","modified_gmt":"2025-10-25T10:07:55","slug":"xllookuplistobjectvalue","status":"publish","type":"page","link":"https:\/\/www.silverice.it\/IpercubeTechWP1\/ipercube-utilities\/excel-functions\/xllookuplistobjectvalue\/","title":{"rendered":"Table Manipulation &#8211; Lookup List Object Value"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Searches for a specified value in the first column of an\u00a0<em>Excel.ListObject<\/em>\u00a0and returns the row index if found.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This function uses the\u00a0<strong>Find<\/strong>\u00a0method on the table\u2019s data body range, looking only in the first column, with\u00a0<strong>LookIn:=xlValues<\/strong>\u00a0and\u00a0<strong>LookAt:=xlWhole<\/strong>\u00a0for exact match. If the value is not found or an error occurs, the function returns\u00a0<strong>0<\/strong>.<\/p>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:75%\">\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-8f761849 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column page-snippet is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:100%\">\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-Geist-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-Geist-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#D4D4D4;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:flex;align-items:center;padding:10px 0px 10px 16px;margin-bottom:-2px;width:100%;text-align:left;background-color:#2b2b2b;color:#c7c7c7\">VB<\/span><span role=\"button\" tabindex=\"0\" style=\"color:#D4D4D4;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>    Public Function xlLookupListObjectValue(ListObject As Excel.ListObject, \n                                            LookupValue As String) As Integer\n\n        If ListObject Is Nothing Then Return 0\n\n        Try\n            Dim FoundCell As Excel.Range = Nothing\n\n            'Attempt to find value in Table's first Column\n            Try : FoundCell = ListObject.DataBodyRange.Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole)\n            Catch ex As Exception : End Try\n\n            'Return Table Row number if value is found\n            If Not FoundCell Is Nothing Then\n                Return ListObject.ListRows(FoundCell.Row - ListObject.HeaderRowRange.Row).Index\n            Else\n                Return 0\n            End If\n        Catch ex As Exception\n            Return 0\n        End Try\n\n    End Function<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M4.5 12.75l6 6 9-13.5\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M16.5 8.25V6a2.25 2.25 0 00-2.25-2.25H6A2.25 2.25 0 003.75 6v8.25A2.25 2.25 0 006 16.5h2.25m8.25-8.25H18a2.25 2.25 0 012.25 2.25V18A2.25 2.25 0 0118 20.25h-7.5A2.25 2.25 0 018.25 18v-1.5m8.25-8.25h-6a2.25 2.25 0 00-2.25 2.25v6\"><\/path><\/svg><\/span><pre class=\"shiki dark-plus\" style=\"background-color: #1E1E1E\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">Public Function <\/span><span style=\"color: #DCDCAA\">xlLookupListObjectValue<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">ListObject<\/span><span style=\"color: #D4D4D4\"> As <\/span><span style=\"color: #4EC9B0\">Excel<\/span><span style=\"color: #D4D4D4\">.ListObject, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                                            LookupValue As <\/span><span style=\"color: #4EC9B0\">String<\/span><span style=\"color: #D4D4D4\">) As <\/span><span style=\"color: #4EC9B0\">Integer<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> ListObject Is<\/span><span style=\"color: #569CD6\"> Nothing<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Try<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #569CD6\">Dim<\/span><span style=\"color: #9CDCFE\"> FoundCell <\/span><span style=\"color: #D4D4D4\">As <\/span><span style=\"color: #4EC9B0\">Excel<\/span><span style=\"color: #D4D4D4\">.<\/span><span style=\"color: #9CDCFE\">Range<\/span><span style=\"color: #D4D4D4\"> =<\/span><span style=\"color: #569CD6\"> Nothing<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #6A9955\">&#39;Attempt to find value in Table&#39;s first Column<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Try : <\/span><span style=\"color: #9CDCFE\">FoundCell<\/span><span style=\"color: #D4D4D4\"> = ListObject.DataBodyRange.<\/span><span style=\"color: #DCDCAA\">Find<\/span><span style=\"color: #D4D4D4\">(<\/span><span style=\"color: #9CDCFE\">LookupValue<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> LookIn<\/span><span style=\"color: #D4D4D4\">:=<\/span><span style=\"color: #9CDCFE\">xlValues<\/span><span style=\"color: #D4D4D4\">,<\/span><span style=\"color: #9CDCFE\"> LookAt<\/span><span style=\"color: #D4D4D4\">:=<\/span><span style=\"color: #9CDCFE\">xlWhole<\/span><span style=\"color: #D4D4D4\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><span style=\"color: #D4D4D4\"> : <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #6A9955\">&#39;Return Table Row number if value is found<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">If<\/span><span style=\"color: #D4D4D4\"> Not FoundCell Is<\/span><span style=\"color: #569CD6\"> Nothing<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #C586C0\">Then<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> ListObject.<\/span><span style=\"color: #DCDCAA\">ListRows<\/span><span style=\"color: #D4D4D4\">(FoundCell.<\/span><span style=\"color: #9CDCFE\">Row<\/span><span style=\"color: #D4D4D4\"> - ListObject.HeaderRowRange.<\/span><span style=\"color: #9CDCFE\">Row<\/span><span style=\"color: #D4D4D4\">).Index<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Else<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">                <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">End If<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        Catch ex As <\/span><span style=\"color: #4EC9B0\">Exception<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">            <\/span><span style=\"color: #C586C0\">Return<\/span><span style=\"color: #D4D4D4\"> <\/span><span style=\"color: #B5CEA8\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">        <\/span><span style=\"color: #DCDCAA\">End<\/span><span style=\"color: #D4D4D4\"> Try<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D4D4D4\">    <\/span><span style=\"color: #569CD6\">End Function<\/span><\/span><\/code><\/pre><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Searches for a specified value in the first column of an\u00a0Excel.ListObject\u00a0and returns the row index if found. This function uses the\u00a0Find\u00a0method on the table\u2019s data body range, looking only in the first column, with\u00a0LookIn:=xlValues\u00a0and\u00a0LookAt:=xlWhole\u00a0for exact match. If the value is not found or an error occurs, the function returns\u00a00.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":349,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-451","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/451","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/comments?post=451"}],"version-history":[{"count":2,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/451\/revisions"}],"predecessor-version":[{"id":453,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/451\/revisions\/453"}],"up":[{"embeddable":true,"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/pages\/349"}],"wp:attachment":[{"href":"https:\/\/www.silverice.it\/IpercubeTechWP1\/wp-json\/wp\/v2\/media?parent=451"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}