LinkBack URL
About LinkBacks
Hello,
I'm working on a spreadsheet for movies. I'm trying to create a formula that pulls data from a specified row number and populates the fields with the data from that specific row.
Any ideas? Thanks!
Hello,
I'm working on a spreadsheet for movies. I'm trying to create a formula that pulls data from a specified row number and populates the fields with the data from that specific row.
Any ideas? Thanks!
An INDEX() function seems perfect for this, if I understand what you are doing.
1) To make copying easier, enter the desired row numbers above each column in "Lookups" (if you don't like this helper row, you can embed the values inside of the INDEX() function in step 2). In A6 I enter 5, B6 is 6, C6 is 7, D6 is 1 -- corresponding to the columns in "acadamy awards data"
2) In A9, I enter a simple INDEX() function =INDEX('Acadamy Awards Data'!$A$1:$H$100,$B$7,A$6). Note the mix of relative and absolute references and copy/paste/fill into B9:D9
Now you can enter whatever row you want in B7, and the corresponding data from the database will be returned.
Did I understand what you wanted well enough?
INDEX() function help file: https://support.microsoft.com/en-us/...2-b56b061328bd
Originally Posted by shg
Depending on how you are going to use it and what flexibility you might want, here's another option (in A9 and copied across):
=INDEX('Acadamy Awards Data'!$A$2:$H$332,$B$7,MATCH(A8,'Acadamy Awards Data'!$A$1:$H$1,0))
(I noticed your headers on LOOKUPS was the same as the other sheet, which would be a requirement for this to work)
In fact, you could do it with one formula in cell A9 to get all columns:
=INDEX('Acadamy Awards Data'!A2:H332,B7,MATCH(A8:D8,'Acadamy Awards Data'!A1:H1,0))
(You don't need to worry about relative/absolute position with this one as no need to copy down or across, although it's probably good practice to add the absolutes so you could copy the formula anywhere and it would still work)
Hi,
The newer spill functions can also do this problem with formulas like..
Formula:
Please Login or Register to view this content.
See the attached.
I've created a Named Range called "dnrData" to use it in the formula.
I should have named my file "Drop ChooseCols Take Lookup" but the file will work..
Last edited by MarvinP; 04-02-2025 at 09:53 PM.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Wow excellent! Did not know about the spill functions but this is exactly what I needed. Other responses are extremely helpful as well. Thank you all
Hi,
The newer spill functions can also do this problem with formulas like..
Formula:Please Login or Register to view this content.
See the attached.
I've created a Named Range called "dnrData" to use it in the formula.
I should have named my file "Drop ChooseCols Take Lookup" but the file will work..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks