VLOOKUP to pull data through.

[LMills77]

Hello,

I have two lists of payments I'm working with. The first list has a list of Transaction Numbers that match up to the transactions.

The transactions have: Payer's name, Merchant Details, Pay Date and Amount. They are all unique.

I have a new, second list that does not have the Transaction Numbers on them. I have close to 1,000 of them. Obviously, this could take me weeks to go through each transaction and add the Transaction Numbers to them. The best way is a VLOOKUP.

How can I pull the Transaction through from the old list to populate the new list I have? I need things explaining in details, so any help would be greatly appreciated!

Lee.

[Sam Capricci]

if the data is by column A through E with A being the transaction #, B the Payer's Name, C the merchant details, D the pay date and E the amount then you will likely need index/match to accomplish this. Vlookup looks to the right of a column to find the details. So =VLOOKUP(A2,Sheet1B2:C100,2,false) looks at the value in A2 then looks in sheet 1 cells B2 through B100 to find the value in A2 then returns the value in C2 through C100 that corresponds to the A2 value. That is what the 2 is for and the "false" means it has to be an exact match.
using index/match can do what a vlookup can do but can go to the left as well. So =INDEX(sheet1B2:B100,match(A2,sheet1C2:C100,0)) looks for the value in A2 in the rows of C2 to C100 and when it finds it, it returns the corresponding (from the same row) the value in B2 through B100.

Hope that helps.

[LMills77]

Hi mate,

Thanks for getting back to me.

I have attached a doc I just put together to show what I mean.

I followed the instructions, but clearly, I've done something wrong (no surprise!)

On the workbook, I've put my calculations on the tab 'Sheet4.' I have the old list on the left and the new list on the right.

I tried to get the transaction numbers on the left to pull through into column S on the right-hand side table.

It pulled the Transaction Numbers through, but they don't match up. The list on the left is filtered by Transaction Number,
while the list on the right is filtered by highest amounts at the top.

Sorry to be a bit retarded! I will pick this up, but this is entirely new to me. I've never used VLOOKUP before!

Thank you,

Lee.

[Sam Capricci]

so instead of this =VLOOKUP(Table15[@[Trans No]],Table,1,FALSE)
I think you need this... =VLOOKUP([@[Trans No]],Table,1,FALSE)
AND, you aren't retarded, you are just new to excel and we've all been there. You'll learn it if you need to.

[LMills77]

Hi Sambo,

Yes, that works. Brilliant.

I do have one question though - I have a project on at work and I have this same problem. But, I don't think I can use the system on this post.

It's hard to explain but, but I saw my supervisor do the VLOOKUP but she searched by the payer's name, merchant details, payment date and payment amount. I saw her put that criteria in on the VLOOKUP box (Function Arguments) but I don't know how she did it. I'm playing around with it now but can't do what she did. She did this because she couldn't search by Transation Number.

Does this make sense? I would like to try and recreate how she did it. It's like she searched more than the one criteria - instead, she searched for 4.

I will try and have a play around now and reply back to you.

Thanks for your help.

Lee.

[Sam Capricci]

I will first tell you that I don't use tables and table references because they cause me time to dig deeper into the data so the attached is without tables.
I set up a section (A through H) with a copy of your data, I gave you examples of vlookups in J through Q and index/match in S through Z.
The cells filled green are the cells that I used to match against. I also tweaked the vlookups to show you how to incorporate a way that you don't have to hard code all the formula (you'll see it in the change from column L to column M where instead of changing the 3 to 4 I just inserted a column D1 function into the formula which equals 4, then as it moves right it goes to column E1 etc. I also added a feature that if the formula returns a 0 leave it blank.
Hope that helps you see how it works.

[LMills77]

Hi Sambo,

I have something to work with now. I'll spend time on this getting my head around it.

Thanks again,

Lee.

[Sam Capricci]

sure, no problem. if that takes care of your question don't forget to mark the post as solved using the thread tools dropdown at the top of this post AND if you are so inclined hitting the little
* Add reputation button at the bottom of one of my posts is always appreciated as it is how we advance on this forum.