Find Identical Values In Other Sheet THEN Make Cell Value Relative To That Location?

[ExcelNoob999]

Hi. Hopefully the topic isn't too confusing.

Basically in 'Sheet1' I have a list of invoice amounts. Next to that are date and reference columns with blank cells.

In Sheet2 is a bank statement which lists payment amounts and has columns complete with the date and reference information.

What I would like to do is (in Sheet1) look for values that are unique and duplicated in Sheet2 (ie don't appear more than once) and THEN to make the value of that cell in Sheet1 the same as the cell which is in Sheet2 on the same row as that identical cell but three columns away (in Column A Sheet2) for the date.

The other value I would like is the payment reference which is the same row but two columns away in Column B.

Is this possible?

For example, in Sheet1, column I8 is an amount of 1000.
In Sheet2, column D are the corresponding bank statement amounts
Assume 1000 appears once in Sheet1 and Sheet2.
I would like to automatically complete the date column (K) in Sheet1 with a value that equal to that unique value row in Sheet2 but three columns away or in Column A Sheet2.

Hopefully that clarifies the situation and doesn't make it more confusing

I'd be grateful for any help, I've been messing about with this for a few days.

 

[VoG]

I think that VLOOKUP should work for that http://www.contextures.com/xlFunctions02.html

 

[ExcelNoob999]

Thanks. I've been trying to do it but I keep getting #REF error with my formula, probably something is wrong. Here is what I came up with:

=VLOOKUP(E6, BankStatement!D:D, BankStatement!A:A, TRUE)

So I want it to lookup E6 in Sheet1 and find the same value in D column in BankStatement and then come up with the corresponding Date value from the same row in column A in the BankStatement sheet.

Does anyone know what's wrong? I think it's something to do with me entering the whole of column A (vertical reference?) and not a horizontal one.

I'd be grateful for some assistance. It feels like I'm getting somewhere, almost

 

[RichardMGreen]

If you're looking left from your initial value, you can't use VLookup (it only look to the right).
You'd need Index/Match to look left.

The formula would be:=
=index(column_to_return_in_sheet2,match(value_to_look_for,column_to_look_at_in_sheet2,0))

The 0 at the end means it looks for an exact match.

 

[ExcelNoob999]

Thanks! I'll have a look at that and maybe try moving the cells I want to the info from to the right. I'll let you know how it goes.

Really impressed with this forum. Quick and useful replies. Unlike that other Excel forum

 

[ExcelNoob999]

Well, the Index and match worked! Moving columns didn't help with the VLOOKUP but it works now!

Thanks so much. This has saved me so much time. Well, maybe I could have done it manually in the two days I've been trying to automate it. But at least it's automated now