To customize use this key.
=arrayformula(if(row([range1])
where
[range1]: range of values to be looked up
[range2]: table of data where look up values are in column 2
N: number of column that has the data you want to returnI've also embedded a spreadsheet here that shows a few examples of how to use this:
Also, if you want to play with this spreadsheet feel free to make your own copy here:
Using arrayformula with vlookup template
Cheers!
What the... why does this work?
ReplyDeleteOh, and thank you very much for saving me a lot of frustration!
ReplyDeleteHow should it be modified in order to update automatically in columns?
ReplyDeleteIf you put that formula above in row 2 of the column that you want to display values, and update the bolded values with your ranges, it should update dynamically. If you are having problems, feel free to write back here with a link to your example.
ReplyDeleteUPDATE: I am facing a similar challenge now, where I need to show inside a cell all of the filtered rows from another spreadsheet matching a key value. But somehow I can't get it to work inside an array formula to update by itself for every row. It produces an error that says "ranges length do not match" (translation by me from Greek)
DeleteCan you post a link to your spreadsheet and I can take a look?
DeleteYou can find a simple one I made, here: https://docs.google.com/spreadsheet/ccc?key=0Auf1OZbEi1vPdG1kZjJKTEIycTA4bmI2aVVnTzhEZkE
DeleteWhat do you want to appear in that column? The Part No. associated with each ID?
ReplyDeleteCan you also allow anyone to edit the spreadsheet?
Opened for edits the spreadsheet. Yes, I'd like associated Part Nos to appear in the column but all of them in the same row.
DeleteI see what you are trying to do now. That is a little more complicated and I'll need some time to sort that out. For now, I'm able to grab the first part number that comes in the list based on your ID but not all of the different part numbers. We will need to employ filter() and/or query().
ReplyDeleteThanks a lot for your help. It's really crucial to me that I solve this puzzle because I will have the same task in other columns/spreadsheets as well. I have thought of query too, but the documentation says it doesn't support dynamic ranges and I had given up on the idea. I'll be waiting for your help, thanks a lot in advance!
ReplyDeleteCheck out your spreadsheet. Top Contributor Adam has a solution for you! I hope that is what you are looking for.
ReplyDeleteHad to revert to a previous version cause someone had deleted his contribution. it seems to be working fine, thank you both!
ReplyDelete