Hello,
I would like to do a Vlookup and return the entire row where the value is located.
I also would like the Vlookup to be dynamic; so if this value is added to another row my Vlookup table will be updated.
I am also open to trying a different method such as Filter, if that would be better.
Any help is greatly appreciated!
Thank you!
I think a sample spreadsheet would help. Could you upload one?
Uploading a book will help greatly but i think waht you need is index not vlookup
The Importance of INDEX - A GUIDE TO INDEX'S OTHER USES
<--- If a post helps hit the star
Temporary.xlsx
I have attached a very simple sample sheet. I would like the data from the first 2 tabs looked up by Item number and placed into the 3rd tab.
I need the entire row where the item number is located to be placed in the 3rd tab, and would like for all rows with matching item numbers to be listed consecutively in the 3rd row.
Thanks so much!
Not sure if this will work for you, but look at this:Temporary.xlsx
Basically, I just copied your existing tables to the Master Sheet, then used your filters in the Item# columns to show all the occurrences of that item
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Thanks for your responce! this method doesn't work for me because I need to be able to see all item numbers. I am basically just trying to group together all rows from different sheets who have matching items numbers.
I havent seen your data...But Did You try and use Match and Index formula
dinesh,
I am trying to use Match and Index but not getting the result I need. Not sure if I am usuing correctly
I have used the following formula(s) to copy the data to the master sheet...unfortunately this is unsorted, and the built in sort in Excel seems unable to do the sort...still working on it, but I think I will call in some help here....:
Formula:
in A2:
=IF(ROWS($A$2:$A2)<=ROWS(Table1),RIGHT(CELL("filename",ODC!$A$1),LEN(CELL("filename",ODC!$A$1))-FIND("]",CELL("filename",ODC!$A$1))),IF(ROWS($A$2:$A2)<=ROWS(Table1)+ROWS(Table13),RIGHT(CELL("filename",Labor!$A$1),LEN(CELL("filename",Labor!$A$1))-FIND("]",CELL("filename",Labor!$A$1))),""))
Dragged down (sheet the information came from..rename sheet and the return is still right)
In B2:
=IF(ROWS($A$2:$A2)<=ROWS(Table1),OFFSET(INDIRECT(CELL("address",Table1)),ROWS($B$2:$B2)-1,COLUMN()-2),IF(ROWS($A$2:$A2)<=ROWS(Table1)+ROWS(Table13),OFFSET(INDIRECT(CELL("address",Table13)),ROWS($B$2:$B2)-ROWS(Table13)-1,COLUMN()-2),""))
(This is the actual data transfer)
Dragged down and across
These will transfer all the information to the sheet you use them in..but everything I have so far tried will not sort them...yet !
darnit...forgot to add the WB
@balston2,
In your sample file you didn't shown us what is your expected output?
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Edit- Change Calculation to Manual
Okay, sort the list, hit the filter in any table column, un-mark blanks,right click on the Item Number header, Sort, A-Z, that should give you the data sorted the way you want..if you need it sorted even more, when you right click, sort,use custom sort, choose the first field to sort(Item Number),add a level,next field,etc..
The mistake I made Initially was not unchecking the blanks and leaving calculation Automatic
Hope this helps
Edit-
Note-
I added the MasterRaw Tab to show what the data looks like before the filters and sorts were applied, just for comparison
Edit 2-
Also, you could sort the table first, then filter the blanks, but I like to keep the data where I can see it
This is what caused me to think the sort wasn't working the first time...
Last edited by dredwolf; 01-12-2013 at 05:45 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks