Hello folks,
I need help figuring out a formula to find a match for cell and then return the value of the title of that column in which the match was found.
Screen Shot 2021-05-03 at 6.20.54 PM.png
If value of H3 is found in any cell between V3 to AA22, then return value of first row of that corresponding column, in I3.
So for e.g. in I3, I want the formula to return back "Bundesliga - Germany".
Thank you for all the help.
Array enter (enter using Ctrl-Shift-Enter)
=INDEX($1:$1,MAX(IF($V$3:$AA$22=H3,COLUMN($V$3:$AA$22))))
Then copy down.
Bernie Deitrick
Excel MVP 2000-2010
Hi Bernie,
Thank you very much for the quick response. But I am getting the "#VALUE" error (attached screenshot).
Attachment 731469
Also what do you mean by "Array enter (enter using Ctrl-Shift-Enter)"? Sorry I am new to Excel language. Thank you.
Well, your screenshot is unviewable - use the insert image command instead of an attachment - but the Ctrl-Shift-Enter is a three key combination similar to Ctrl-Alt-Del. After pasting the formula into the formula bar, press and hold both the Ctrl and Shift keys as you press Enter - if you do it correctly, Excel will place {} around the formula. The array entry instructs Excel to handle the interior parameters of the functions as arrays, processing them individually instead of as a group.
-
If you cannot figure out how to get it to work, attach your example workbook - there are instructions in gold at the top of the forum page/screen.
Hi Bernie,
I think I just uploaded the worksheet. If you don't see it please let me know.
Try
=IFERROR(INDEX($1:$1,,SUMPRODUCT(($V$3:$AO$25=H3)*(COLUMN($V$1:$AO$1)))),"")
Entered with Ctrl+Shift+Enter
Of course, the first club in H3 is not in your table
In I3 then copy down
=IF($H3="","",IFERROR(INDEX($1:$1,AGGREGATE(15,6,COLUMN($V$1:$AO$1)/($V$3:$AO$40=$H3),1)),""))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Wow. Thank you very much guys. You guys are life saviors.
3 different formula. All of them work.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Hi guys,
So finally was able to figure out what and how array formula work in Excel. Thanks Bernie.
One more question. Is there a way to change the formula to work if the list of all the clubs were in the next tab (Clubs) and not in the main tab (Players)? I realized I can't really edit or sort the initial rows without messing up all the club names in the far right, so I will have to move the entire list of club names in the next tab. Attached workbook.
I tried to work with the formula you guys gave me, but I can't get the concept of $1:$1, which doesn't seem to work if I simply switch that to highlight the entire first row of the "Clubs" tab.
Thank you very much.
You need to reference the other sheet's ranges in your formula - all except H3, of course.
I have attached the corrected spreadsheet.
You were wrong about your assumption that you can only sort entire sheets - you can sort any part of a worksheet that you want by selecting it prior to using the sort option. If you have ranges that abut each other, Excel will ask if you want to continue or use the entire contiguous range. You would just continue.
Thanks for the additional information Bernie. This forum is really fast. Just got rid of my brain freeze from couple of days. The formula works perfectly. Hope you have a great weekend.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks