Hello,
I have a database of records - each record consists of (say) 10 rows and 10 columns of data - ie not one record per row. The top left hand cell of each record has an identifier.
I want to look up the identifier and pick the cell that is x columns to the right and y rows down. Is there a way of doing this using the vlookup and offset functions? The vlookup obviously works for x columns to the right but how do I shift this a number of rows down.
Thanks for your help.
-- Rick
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks - Ill re-post as a new thread.
Do not duplicate your post. Just add the file to this thread. Please read our forum rules and pay attention to the item on double posting.
OK - apologies.
I have attached an example which hopefully explains what I am trying to do. I understand I could create a new lookup key combining the
ID and data label but in reality the database is a lot more complicated than this with data in multiple columns.
I just want to be able to look up the id and then pick the data that is x columns to the right and y rows down.
Thanks for your help.
-- Rick
Are you required to use the VLOOKUP() function? In this case, I would probably use the MATCH() function to find the ID group of rows, then use the Datan value to determine the row offset. Then put that into an INDEX() function to get the desired result.
1) Enter "offset" values in G5:I5. 0 in G5, 1 in H5, 2 in I5.
2) =INDEX($B$4:$D$20,MATCH($F6,$B$6:$B$20,0)+G$5,3)
Would that be acceptable?
Originally Posted by shg
That's perfect.
Thanks very much.
-- Rick
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks