Excel Magic Trick 360: Part 3: Return Multiple Items From One Lookup Value for Row w Formula
Вставка
- Опубліковано 11 жов 2024
- Download Files:
people.highlin...
Duplicate Lookup or Lookup 1 value, return many for a row Part 3. See a formula that looks up one value and returns multiple items. One To Many Relationship. See an array formula that uses the functions: COUNTIF, IF, COLUMNS, INDEX, SMALL and ROW.
Related Videos: Excel Excel Magic Trick 359: Part 2: Return Multiple Items From One Lookup Value for Column, Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table
Even after 10 years, this guy and his vids find a way to help folks. I used this today. There may be more modern ways to do the same thing, but I like how this one worked for me.
Glad it helped, pa beader!!!! Thanks for your support : )
You are welcome!
Thanks!
Merry Christams to you too!!
When you have a formula that contains an array operation and the operation is in a function argument that is not innately programmed to handle array operations, you must use Ctrl + Shift + Enter. See the "Array Formula" playlist that I sent you link about.
ThankYou!ThankYou!ThankYou!ThankYou!ThankYou!ThankYou! I learned so much from this video. You have no idea!
An oldie but a goodie. Just tried in Excel 2013 and it works.
Yes, indeed! Thanks for stopping by in the comments, Toby!!!!
Did you use Ctrl + Shift + Enter when entering your formula?
Thank you so much for sharing this! It was so easy to follow and has solved a problem I’ve been having for two days!
I am glad that the video helps you, Nausheen!!!! Thanks for your comment : )
That is amazing, you just solved in one formula so many problems I've been asking myself for the past two years. Thanks again!
I used the concatenate function (text+date) + countif to automatically return multiples values of a specific week.
Thanks again!!!
I don't know what you mean by "filter through duplicates". Do you want to get a unique list. If so try these two videos:
Excel Magic Trick 82: Advanced Filter Extract Unique Records
or
Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array...
Use ROWS rather than COLUMNS and lock the row (number) reference rather than the column (letter).
Incredibly useful. This saved me a couple of hours of crunching. I was not using a dropdown because I was saving to a specific format for a website spreadsheet. I had to unlock the row portions of the arrays to allow copy-pasting to the hundreds of lines of my sheet for this usage, since a drop-down type cell doesn't work for this usage.
Done like dinner though. Time saved. Excel wins.
Were you flowing down the formula? I can't seem to make it work. I am assuming you removed $ in specific spots. Where were you removing the $ from?
I unlocked the row portions, so $A$1 is locked row and locked column.
A$1 is unlocked row and locked column.
Be aware that copy-paste works differently from cut-paste with respect to unlocked cell references, so try to use copy-paste if you need to do any fine-tuning...
Also holds true for conditional formatting.
On my sheet, I have a number of criteria for a few different individuals in different roles to use. I use conditional formatting in conjunction with my lookups so I can have a visual check of anomalous readouts for the cells. But fine tuning the sheet will mess up formulas for conditional formatting as well as formulas in cell lookups that are not locked.
The most common way they get screwed up is if you have a header row and some formulas or lookups start at row B and you paste in a large number, but end up with an offset of one row.
this video was a perfect solution for my problem, thank you!
Thx so much, I solved the problem.
That is a beautiful work of spreadsheet art! Thanks for the help!!!!
Thank you for this awesome formula.
THE best place to have back and forth dialog to get Excel solutions is the Mr Excel Message Board:
mrexcel [dot] com/forum
It is hard to do that here at UA-cam.
When you post at this site, you must post your data set, your formula and a description of what the problem is.
After you post, you can send me the link to the post and I can take a look.
The site is not affiliated with excelisfun at UA-cam - it is a different site altogether.
I got it to work. Feel foolish. Wasn't using ctr shift enter. Thanks.
Doug
Great tick, just one question, if I have long list of characters and it will not be possible to use the drop-down box, rather I will need a copy and paste form that will evaluate each new value...any hint...thanks
Hi ExcellsFun. Excellent trick! I have only one problem. I need to copy the formula down as well. I've tried to strategically remove dollar signs to allow it to copy properly but must be missing something. Would you have time to help with this? I can give more info if you need it. Thanks
Doug
Hi ExcellsFun. Your video helped me a lot! On the other side I have a problem that I cannot predict how many times the searched values will appear. Is it possible to return multiple items into single cell (instead of separate columns ) ? Thanks!
@AdamkiewiczJ , I don't have a trick for that. Try THE best Excel question site:
mrexcel [dot] com/forum
Thanks for the quick reply.This will allow me to copy the cell both across and down?
Would you be able to explain how I can do this but when the data I am looking up is in different columns rather than different rows? For example if SIP DEVICE was in the cells A2,B2& C3. Also finally is it possible to have the formula display the cell reference rather than the value?
This is Awesome , thank you :)
Hi is there any way that instead of only checking for the type of device, you could also check for a second condition?? i tried AND as well as a nested if but it doesnt seem to work
Hi
Thanks for taking the time to produce these videos, they are a real help.
So, here is my question,
I have a one to many, column lookup but I want to change the output from a column to a row...
=INDEX($C$1:$C$25;MATCH(0;COUNTIF($J$1:J1;$C$1:$C$25)+IF($A$1:$A$25$F$2;1;0);0))
The intent is to use the value in F2 to search column A for matches and return all unique column C values from the same row as the column A F2 matches.
Is there a way to get this formula to list the unique values along a row rather than down a column?
Again thank you for the videos.
Hi ExcellsFun, Do you know of a trick that returns the values for the approximate match as well?
Thanks a lot first of all for the extremely good explanation in details. However I would like to ask you for some further support if I may please - I have a source table with about 3500 rows and inside the elements might have from 1 to 7 occurances. What I encountered as a problem is that when I try to use the formula on the whole span or even more than 25 rows of the source table, i get an error. I get a message that the formula can not exceed 8132 symbols. Do you have an idea how could be overwhelmed this issue? In case of need of course I could share the excel file.
Thank you in advance!
I've tried this for a large table: it shows the correct number of entries in the rows, but only the correct value in the first item - the following ones have #SUM errors - and I can't work out why. Anyone else had this problem?
True jedi master. This shit is ridic. Bravo!
Great video. I also have a question: I have a huge vendor table/database with vendor name, address, phone #, etc. How can I do a lookup on part of the name and retrieve all vendors that match that criteria. For example if I type "alarm" in a cell I would like to extract a list of vendor that match the criteria: "A1 Alarm, US Alarm Systems, etc." The data resides in a separate sheet. If it was on the same sheet I could use auto filter, but I was wondering if I could do this with a lookup or index formula. Thank you
Hi ExcellsFun! I have been working on a multiple return from a single lookup for days now...I have watched this video over and over, checked every command over and over and I am still not having any success. I have narrowed the problem down to the 'SMALL' command. The return value is #VALUE?? Is there anyway you can help me????
How can I apply this to perform a fuzzy search? For example if cell D2 on your sheet contains the text "IP" and it would still return the same results as "SIP DEVICE". I've also reviewed EMT986 and it also appears to require an exact matching search string. As another example, I am trying to search a dictionary and return every result that contains "can" (can, cannot, canoe).
Hi! I used this in my row lookups. It worked fine with those with multiple values. But when the item has only 1 value, then it returns me with blank. How should I fix this?
Hi, I dont know what wrong it is, I got the first result of Type ID 1 = X1 but for the Type ID 2 that will so #NUM!. I checked it use F9, it responded X2 for the formula. Can u explain it???
I have no knowledge of that. You can try the Excel help forum:
mrexcel [dot] com/forum
Maybe someone there can help.
Dear ExcelIsFun..
why Ctrl+Shift+Enter
how you understanding the this formula Ctrl+Shift+Enter
plz tell me tips ;)
Hello I thought your video was interesting, maybe you can help me further, I have a column with an ID in the Excel sheet (EA3: AE22) in addition to the names (there are 20), in addition I have 3 columns, in cell AE2 I also have an Id the column next to it you will also find the name (that changed) that I want to look for in A3: CV21, (each name has 3 columns of data apart from the name), I want to retrieve that data and place it in the right place, Is that possible? For example, in AE2 the name Malacor comes that we find in B2, Now I want to retrieve the data from that column that is in column C and place it with the name (in our case, B2 can be found) and also find in EB2, the data comes on EC4, if you need more info ask, thank you?
I've done the formula but with a database of 10,000 records, it just returns #n/a
it is very difficult very large formula
:)
You are welcome!
Great tick, just one question, if I have long list of characters and it will not be possible to use the drop-down box, rather I will need a copy and paste form that will evaluate each new value...any hint...thanks basically copying across but also verticaly
You are welcome!