Return column number of last occurence of specific value

[Sir babydum GBE]

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.

 

[Momentman]

There must be a non-array way out of this but here,

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Black Widow	24
2				Black Widow			Black Widow			Black Widow														Black Widow
Sheet5

Cell Formulas
Range	Formula
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]

Brilliant! Thanks SO much.

 

[lrobbo314]

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]

Ok, thanks both. Much appreciated

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

 

[FDibbins]

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]

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.