Download workbook has a bunch of other formulas from these comments. Cool fromulas like: =XLOOKUP(D6:D14,List01,ADDRESS(ROW(List01),COLUMN(List01),4),"") Check it out!!!!
Hi Guys. Great solutions! Here is one I came up with after noodling with it. It is unusual, but gets the correct result: =IF(MAX(IF(D18=List01,ROW(List01)))=0,"","B"&MAX(IF(D18=List01,ROW(List01)))). Kudos for all the great learning and inspiration created by your duels. Thumbs up!!
Hi Mike, thanks for an interesting duel :-)) Assuming the range of "List01" is in the range of columns A through Z, we can get rid of the volatile functions (but only if the assumption is true) =IFERROR(CHAR(COLUMN(List01)+64) & MATCH(D6:D14,List01,0),"")
Ctrl+Shft+A after typing formula name is amazing. I used Ctrl+A for formula argument box to get inside the formula. I liked Mike's version SUBSTITUTE, CELL, ADDRESS, INDEX & MATCH combo. Cheers :)
Great challenge!! An alternative without ADDRESS but with CELL , lists could be anywhere on the worksheet: =LET(a,CELL("address",List01),r,ROW(INDEX(List01,1,1))-1,lc,MID(a,2,SEARCH("$",a,2)-2),x,XMATCH(List02,List01)+r,rs,lc&x,IFNA(rs,"")) a - address of first item in list01 , lc - extracts the letter of the column , r - row first item -1 x - xmatch array rs - joins the letter with the row nrs.
I am pretty sure OFFSET is the best solution for this situation! =IF(ISNA(MATCH(D5,List01,0)),"",SUBSTITUTE(CELL("address",OFFSET($B$4,MATCH(D5,List01,0),,1,1)),"$","")) Simple Offset substituted everything for index, so u don't need that column #2 and etc... idk I think offset is better for it! great vid guys, keep it up
@@excelisfun much easier to read than screen tip. Not always useful but the screen tip often is in the way at least for me. Yes you can drag it around but putting it directly in the formula often saves valuable real estate and improves viewability. I often build long expressions not from the screen tip but by watching the formula bar or even building in the formula bar. That works best if you do not need the screen tip! CSA is a valuable option.
I've been following your channel for years and I learnt so much! Thank you for your hard work and effort. I've fallen in love with data and data analysis, I'm thinking of following a career in this. Any suggestions? I am a CPA by profession
I passed that CPA exam back in 2002 and also, like you, feel in love with Data Analysis. Within accounting that is tons of data analysis and reporting. As a CPA if you just study on your own and then say in resume that you have X skills, you should be able to do it!
Another approach with address =LET(la,List01,lb,List02,a,ADDRESS(ROW(la),COLUMN(la),4),x,XMATCH(lb,la),IFNA(INDEX(a,x),"")) where a - is the full list of all addresses in List01, that will be used in INDEX xmatch extraction
@@excelisfun I really like your channel. Here is another version using address. =IFNA(ADDRESS(ROW(OFFSET(List01,XMATCH(D6:D14,List01)-1,)),COLUMN(List01),4),"")
One last one. Here is a version of Excel Wizard's solution using CELL =SUBSTITUTE(XLOOKUP(D6:D14,List01,CELL("address",OFFSET(List01,ROW(List01)-ROW(B6),)),""),"$",)
:) =IFNA(CHAR(COLUMN(B5)+64)&ROW(B5)+XMATCH(D6:D14,List01),""), =IFNA(ADDRESS(ROW(B5)+XMATCH(D6:D14,List01),COLUMN(B5),4),""), =XLOOKUP(D6:D14,List01,ADDRESS(ROW(List01),COLUMN(B5),4),"") and =XLOOKUP(D6:D14,List01,CHAR(COLUMN(B5)+64)&ROW(List01),"") -- the last 2 are the same as excel wizard but you don't need to put the table name in the column formula
Download workbook has a bunch of other formulas from these comments. Cool fromulas like: =XLOOKUP(D6:D14,List01,ADDRESS(ROW(List01),COLUMN(List01),4),"") Check it out!!!!
Hi Guys. Great solutions! Here is one I came up with after noodling with it. It is unusual, but gets the correct result: =IF(MAX(IF(D18=List01,ROW(List01)))=0,"","B"&MAX(IF(D18=List01,ROW(List01)))). Kudos for all the great learning and inspiration created by your duels. Thumbs up!!
That's awesome Bill and Mike... different ways to achieve the same Goal
Thanks (Nabil Mourad)
Go Team!!!
Absolutely awesome! Thanks Mike and Bill
You are welcome, David!!!
Spectacular duel as always. The fastest gunmen in the West-Excel !!
Glad you like the fast guns!!!
That was great guys....so great I think I'll watch it again on Mr.Excel's channel. Thanks for the great solutions...
Yes, me too. I watch in in both places too : ) : )
Great solutions. I prefer old school in this case
Old School Rules ; )
Wow Amazing Mike. I love it. Thank you👍
You are welcome , Muhammad!!!!
Hi Mike, thanks for an interesting duel :-))
Assuming the range of "List01" is in the range of columns A through Z, we can get rid of the volatile functions (but only if the assumption is true)
=IFERROR(CHAR(COLUMN(List01)+64) & MATCH(D6:D14,List01,0),"")
Thanks, O Poet : ) : ) : ) Very cool. But as you hint, those are not usually safe assumptions ; )
@@excelisfun The GREAT Bill Szysz. I love it when he posts in here!! : )
@@johnborg5419 Me too : ) : ) : ) : ) : ) : ) : )
Ctrl+Shft+A after typing formula name is amazing. I used Ctrl+A for formula argument box to get inside the formula. I liked Mike's version SUBSTITUTE, CELL, ADDRESS, INDEX & MATCH combo. Cheers :)
Cool, Sachin : )
Beautiful, Thanks Mike!
You are welcome, Luciano!!!
Thank you sir for wonderful video
You are welcome!
Great challenge!! An alternative without ADDRESS but with CELL
, lists could be anywhere on the worksheet:
=LET(a,CELL("address",List01),r,ROW(INDEX(List01,1,1))-1,lc,MID(a,2,SEARCH("$",a,2)-2),x,XMATCH(List02,List01)+r,rs,lc&x,IFNA(rs,""))
a - address of first item in list01 , lc - extracts the letter of the column , r - row first item -1 x - xmatch array rs - joins the letter with the row nrs.
Thanks gr cr0912 : ) I have added it to the Excel workbook : )
Thanks Mike. Old School was great : )
Glad you like all the formula fun, John : )
Boom!Really Wicked Formulas..Thank You Mike/Mr Excel :)
Boom!!! You are welcome, darryl : ) : ) : )
I am pretty sure OFFSET is the best solution for this situation!
=IF(ISNA(MATCH(D5,List01,0)),"",SUBSTITUTE(CELL("address",OFFSET($B$4,MATCH(D5,List01,0),,1,1)),"$",""))
Simple Offset substituted everything for index, so u don't need that column #2 and etc... idk I think offset is better for it!
great vid guys, keep it up
Thanks for adding to the Team, Tornike! I have added your solution to the download workbook : )
And...point goes to Mike 😃
Thanks for pointing that out, zarko : ) : )
CTL+SHIFT+A is great shortcut. Should be taught in EXCEL classes--maybe it is!?!
I am not sure why you need the keyboard when we already have the screen tip? right?
@@excelisfun much easier to read than screen tip. Not always useful but the screen tip often is in the way at least for me. Yes you can drag it around but putting it directly in the formula often saves valuable real estate and improves viewability. I often build long expressions not from the screen tip but by watching the formula bar or even building in the formula bar. That works best if you do not need the screen tip! CSA is a valuable option.
@@richardhay645 That is why it is so cool that Microsoft gives us many ways to do each task : ) : )
@@excelisfun Another bonus of CSA--it puts the screen tip in the Formula Bar!!
I've been following your channel for years and I learnt so much! Thank you for your hard work and effort.
I've fallen in love with data and data analysis, I'm thinking of following a career in this. Any suggestions? I am a CPA by profession
I passed that CPA exam back in 2002 and also, like you, feel in love with Data Analysis. Within accounting that is tons of data analysis and reporting. As a CPA if you just study on your own and then say in resume that you have X skills, you should be able to do it!
@@excelisfun thanks so much! You are an inspiration
Another approach with address
=LET(la,List01,lb,List02,a,ADDRESS(ROW(la),COLUMN(la),4),x,XMATCH(lb,la),IFNA(INDEX(a,x),""))
where a - is the full list of all addresses in List01, that will be used in INDEX xmatch extraction
Thanks, teammate : ) : ) : ) : )
Request you please resolve following formula. =SUMPRODUCT(1/COUNTIF(G3:G4915,G3:G4915)) #if i keep bank rows formula is not working
I have no idea.
Try this great Excel question site: mrexcel.com/forum
how about simple one: ="B"&XMATCH(D9,B:B)
Love it : ) If you don't insert a column, that is the shortest for sure : ) : ) : ) : )
I added your contribution to the download workbook : )
Here is a version that uses CELL and spills
=IFNA(SUBSTITUTE(CELL("address",OFFSET(List01,XMATCH(D6:D14,List01)-1,)),"$",""),"")
Thanks for contributing to the Team : ) I have added your formula to the download workbook!
@@excelisfun I really like your channel. Here is another version using address.
=IFNA(ADDRESS(ROW(OFFSET(List01,XMATCH(D6:D14,List01)-1,)),COLUMN(List01),4),"")
One last one. Here is a version of Excel Wizard's solution using CELL =SUBSTITUTE(XLOOKUP(D6:D14,List01,CELL("address",OFFSET(List01,ROW(List01)-ROW(B6),)),""),"$",)
:)
=IFNA(CHAR(COLUMN(B5)+64)&ROW(B5)+XMATCH(D6:D14,List01),""), =IFNA(ADDRESS(ROW(B5)+XMATCH(D6:D14,List01),COLUMN(B5),4),""), =XLOOKUP(D6:D14,List01,ADDRESS(ROW(List01),COLUMN(B5),4),"") and =XLOOKUP(D6:D14,List01,CHAR(COLUMN(B5)+64)&ROW(List01),"") -- the last 2 are the same as excel wizard but you don't need to put the table name in the column formula