Solved: Matching Multiple Cells

[intheblack]

Hi. this is my first post here, I really need help :-). I've been playing around with solutions to this problem for a while now and have decided to come to a forum to perhaps open my eyes. I've tried formulas (index, Match and the lookup functions) but don't seem to be getting anywhere. Not sure because I'm trying to match both text and numbers?
I'll explain the objective and see what you guys think

The DATA sheet is downloaded from our broker daily and has all the transactions for the day. What I am trying to do is match the data "automatically" with the clients. Up until now I have done it manually line by line, but sometimes over 200 transactions can drive one batty

The match sheet is where the client details are entered. What needs to happen is that the cells O & P (unit number and unit price) need to be "pulled/matched" from the DATA sheet. There are 4 possible criteria to match with:

The account # (col A ) on the match sheet with Client No. (Col F) on the DATA sheet
BUY or Sell (Col E) on the match sheet with BUY/SELL (Col J) on the DATA sheet
ASX Code (col G) on the match sheet with STOCK (col K) on the DATA sheet
Client # (Col C) on the match Sheet with ORDER TAG (Col AB) on the DATA sheet


So if all match (all 4 have to match as there may be many buys/sells of the same stock)

QTY (Col L) from data sheet should be placed/filled in Unit Number (Col O) of the match sheet.
and
AVG PRICE (col M) from the data sheet should be placed/filled in Unit Price (Col P) of the match sheet.

I hope that makes some sense. I have attached a sample spreadsheet to try and give you a better understanding of what I am looking for.

Does anyone have a solution?

[JimmyTheHand]

Hi and welcome to VBAX

This problem is easily solved by introducing two helper columns. They are colored light green in the attached sample. See the formulas: in them, the 4 important values are concatenated. So for each row you get a single string, which contains all 4 values that need to be compared, and you can base VLookUp fuunction on this string.

HTH

Jimmy

[Bob Phillips]

You can also do it with an array formula

=IF(NOT(ISNA(MATCH(1,(DATA!$F$2:$F$20=match!$A2)*(DATA!$J$2:$J$20=match!$E2 )*(DATA!$K$2:$K$20=match!$G2)*(DATA!$AB$2:$AB$20=match!$C2),0))),
INDEX(DATA!L$2:L$20,MATCH(1,(DATA!$F$2:$F$20=match!$A2)*(DATA!$J$2:$J$20=ma tch!$E2)*(DATA!$K$2:$K$20=match!$G2)*(DATA!$AB$2:$AB$20=match!$C2),0)),"")

[intheblack]

Hi JimmytheHand & XLD, Thank you both, that's very cool.
There is something that I neglected to mention. When I match the trades manually, I highlight each one by row(on the DATA) sheet, that way when i'm done I can see the unhighlighted trades must either be from another day or there is a another kind error.
Is there a way to incorporate that into the formula/s?

[Bob Phillips]

Use the formula in conditional formatting.

[intheblack]

Thanks :-)

[intheblack]

Hi XLD,
Excel doesn't let me reference other work sheet with conditional formatting, it flashes an error. Using the same scenario as the original question. I have multiple "match sheets" and only one "DATA" sheet, the rows on the DATA sheet need to be highlighted when they are matched in the match sheets. Any ideas?

[Bob Phillips]

You have to name the range(s) i the other worksheet and use that name in CF.

[intheblack]

Thanks again.