Hi All,
I've been looking on the internet for hours and haven't had any luck.
I need to update one file with corresponding information with another file. They have one link which is the invoice number.
I was considering using vlookup to get the whole table array as a result, but for some reason that is not easy.
Attached are two example worksheets.
Thank you!
Eric
Try this:
In D2 of the AP Data Workbook:Formula:
Please Login or Register to view this content.
In E2 of the AP Data Workbook:Formula:Please Login or Register to view this content.
Then fill down.
You could also use this in D2:Formula:Please Login or Register to view this content.
and this in E2:- MooFormula:Please Login or Register to view this content.
Last edited by Moo the Dog; 01-28-2013 at 02:06 PM.
That works, but I was hoping for something more automated. I have about 150 columns so I will have to change the column index each time.
Last edited by ebdmbfan; 01-28-2013 at 05:45 PM.
You wouldn't necessarily have to - it depends on how your data is set up... if you will be pulling column B, then column C, then column D, etc. for 150 columns, you could use something that increments columns like this:
=VLOOKUP($B2,'[Invoice Data EX.xlsx]Sheet1'!$A:$EZ,COLUMNS($A:B),0)
You can put that in D2 of the AP Data workbook, fill down, and fill right, and it will pull the data from each successive column into your workbook. Adjust the column references to suit your needs ($A:$EZ) may be too many, or too few - that would cover 156 columns.
- Moo
That works great, new question. It is returning 0 for blank cells, how can I change that?
Do you want it to show something or do you want a blank cell?
- Moo
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks