Return column number of last occurence of specific value

Sir babydum GBE

New Member
Joined
Nov 28, 2009
Messages
16
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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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 x:publishsource="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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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 x:publishsource="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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

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

Which adblocker are you using?

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
Back
Top