# Return column number of last occurence of specific value

#### Sir babydum GBE

##### New Member
Hi

Is it possible for a lookup formula to check across a row of data and return the column number of the last occurrence of a value?

For example

I input a string in A1, let's say "Black Widow". B1 contains a formula that looks for "Black Widow" in cells A2 to Z2.

"Black Widow" occurs in cells D2, G2, J2 and X2.

So, because X2 is the furthest occurrence of "Black Widow", the formula returns "24" (X is the 24th Column).

Thanks in advance.

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Momentman

##### Well-known Member
There must be a non-array way out of this but here,

Excel 2010
ABCDEFGHIJKLMNOPQRSTUVWX
1Black Widow24
2Black WidowBlack WidowBlack WidowBlack Widow
Sheet5
Cell Formulas
RangeFormula
B1{=LARGE(IF(\$A\$2:\$Z\$2=A1,COLUMN(\$A\$2:\$Z\$2)-COLUMN(\$A\$2)+1),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

#### Sir babydum GBE

##### New Member
Brilliant! Thanks SO much.

#### lrobbo314

##### Well-known Member
There must be a non-array way out of this but here,

Here is a non-array version.

<style id="Book1_10876_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1510876 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6310876 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6410876 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book1_10876" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=1096 style='border-collapse: collapse;table-layout:fixed;width:823pt'> <col width=28 style='mso-width-source:userset;mso-width-alt:1024;width:21pt'> <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'> <col width=35 style='mso-width-source:userset;mso-width-alt:1280;width:26pt'> <col width=29 style='mso-width-source:userset;mso-width-alt:1060;width:22pt'> <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'> <col width=28 span=2 style='mso-width-source:userset;mso-width-alt:1024; width:21pt'> <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'> <col width=30 style='mso-width-source:userset;mso-width-alt:1097;width:23pt'> <col width=25 style='mso-width-source:userset;mso-width-alt:914;width:19pt'> <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'> <col width=29 style='mso-width-source:userset;mso-width-alt:1060;width:22pt'> <col width=27 style='mso-width-source:userset;mso-width-alt:987;width:20pt'> <col width=34 style='mso-width-source:userset;mso-width-alt:1243;width:26pt'> <col width=31 span=2 style='mso-width-source:userset;mso-width-alt:1133; width:23pt'> <col width=29 style='mso-width-source:userset;mso-width-alt:1060;width:22pt'> <col width=31 style='mso-width-source:userset;mso-width-alt:1133;width:23pt'> <col width=29 style='mso-width-source:userset;mso-width-alt:1060;width:22pt'> <col width=28 span=2 style='mso-width-source:userset;mso-width-alt:1024; width:21pt'> <col width=31 style='mso-width-source:userset;mso-width-alt:1133;width:23pt'> <col width=30 style='mso-width-source:userset;mso-width-alt:1097;width:23pt'> <col width=35 style='mso-width-source:userset;mso-width-alt:1280;width:26pt'> <col width=100 style='mso-width-source:userset;mso-width-alt:3657;width:75pt'> <tr height=20 style='height:15.0pt'> <td height=20 class=xl1510876 width=28 style='height:15.0pt;width:21pt'></td> <td class=xl6410876 width=100 style='width:75pt'>A</td> <td class=xl6410876 width=35 style='width:26pt'>B</td> <td class=xl6410876 width=29 style='width:22pt'>C</td> <td class=xl6410876 width=100 style='width:75pt'>D</td> <td class=xl6410876 width=28 style='width:21pt'>E</td> <td class=xl6410876 width=28 style='width:21pt'>F</td> <td class=xl6410876 width=100 style='width:75pt'>G</td> <td class=xl6410876 width=30 style='width:23pt'>H</td> <td class=xl6410876 width=25 style='width:19pt'>I</td> <td class=xl6410876 width=100 style='width:75pt'>J</td> <td class=xl6410876 width=29 style='width:22pt'>K</td> <td class=xl6410876 width=27 style='width:20pt'>L</td> <td class=xl6410876 width=34 style='width:26pt'>M</td> <td class=xl6410876 width=31 style='width:23pt'>N</td> <td class=xl6410876 width=31 style='width:23pt'>O</td> <td class=xl6410876 width=29 style='width:22pt'>P</td> <td class=xl6410876 width=31 style='width:23pt'>Q</td> <td class=xl6410876 width=29 style='width:22pt'>R</td> <td class=xl6410876 width=28 style='width:21pt'>S</td> <td class=xl6410876 width=28 style='width:21pt'>T</td> <td class=xl6410876 width=31 style='width:23pt'>U</td> <td class=xl6410876 width=30 style='width:23pt'>V</td> <td class=xl6410876 width=35 style='width:26pt'>W</td> <td class=xl6410876 width=100 style='width:75pt'>X</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6410876 style='height:15.0pt'>1</td> <td class=xl6310876>Black Widow</td> <td class=xl6310876 align=right style='border-left:none'>24</td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> <td class=xl6310876 style='border-left:none'> </td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6410876 style='height:15.0pt'>2</td> <td class=xl6310876 style='border-top:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'>Black Widow</td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'>Black Widow</td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'>Black Widow</td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'> </td> <td class=xl6310876 style='border-top:none;border-left:none'>Black Widow</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=28 style='width:21pt'></td> <td width=100 style='width:75pt'></td> <td width=35 style='width:26pt'></td> <td width=29 style='width:22pt'></td> <td width=100 style='width:75pt'></td> <td width=28 style='width:21pt'></td> <td width=28 style='width:21pt'></td> <td width=100 style='width:75pt'></td> <td width=30 style='width:23pt'></td> <td width=25 style='width:19pt'></td> <td width=100 style='width:75pt'></td> <td width=29 style='width:22pt'></td> <td width=27 style='width:20pt'></td> <td width=34 style='width:26pt'></td> <td width=31 style='width:23pt'></td> <td width=31 style='width:23pt'></td> <td width=29 style='width:22pt'></td> <td width=31 style='width:23pt'></td> <td width=29 style='width:22pt'></td> <td width=28 style='width:21pt'></td> <td width=28 style='width:21pt'></td> <td width=31 style='width:23pt'></td> <td width=30 style='width:23pt'></td> <td width=35 style='width:26pt'></td> <td width=100 style='width:75pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>
<style id="Book1_10883_Styles"><!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";}.xl1510883 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6310883 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}.xl6410883 {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:11.0pt; font-weight:700; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:center; vertical-align:bottom; border:.5pt solid windowtext; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}--></style></head>******><!--[if !excel]>  <![endif]--><!--The following information was generated by Microsoft Excel's Publish as WebPage wizard.--><!--If the same item is republished from Excel, all information between the DIVtags will be replaced.--><!-----------------------------><!--START OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD --><!-----------------------------><div id="Book1_10883" align=center xublishsource="Excel"><table border=0 cellpadding=0 cellspacing=0 width=543 style='border-collapse: collapse;table-layout:fixed;width:408pt'> <col width=50 style='mso-width-source:userset;mso-width-alt:1828;width:38pt'> <col width=493 style='mso-width-source:userset;mso-width-alt:18029;width:370pt'> <tr height=20 style='height:15.0pt'> <td colspan=2 height=20 class=xl6410883 width=543 style='height:15.0pt; width:408pt'>Formulas</td> </tr> <tr height=20 style='height:15.0pt'> <td height=20 class=xl6310883 style='height:15.0pt;border-top:none'>\$B\$1</td> <td class=xl6310883 style='border-top:none;border-left:none'>=LOOKUP(COUNTIF(\$A\$2:\$Z\$2,A1),1/(\$A\$2:\$Z\$2=A1),COLUMN(\$A\$2:\$Z\$2))</td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=50 style='width:38pt'></td> <td width=493 style='width:370pt'></td> </tr> <![endif]></table></div><!-----------------------------><!--END OF OUTPUT FROM EXCEL PUBLISH AS WEB PAGE WIZARD--><!-----------------------------></body></html>

#### Sir babydum GBE

##### New Member
Ok, thanks both. Much appreciated

So which is better - array or non-array, and why?

#### FDibbins

##### Well-known Member
If your data set is not too big, it wont make a difference either way, but ARRAY formulas can become resource-intense if used on large data sets

#### lrobbo314

##### Well-known Member
If you have a very large data set, and you are using multiple array formulas, then it can slow down you workbook.

MomentMan's solution is an array because of the Ctrl+Shift+Enter.

Mine is technically not an array formula because you don't need to hit Ctrl+Shift+Enter, but the Lookup function can handle array arguments, so I don't think there is much of a difference really.

It would be interesting to see the calculation times on a large dataset for each of the formulas to see if one is faster than the other.

But, if your dataset is relatively small, it's really just a personal choice of which you would prefer.

Replies
11
Views
300
Replies
6
Views
204
Replies
5
Views
203
Replies
1
Views
141
Replies
5
Views
245

Threads
1,191,555
Messages
5,987,260
Members
440,087
Latest member
Ruppert23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back