Reconciliation formula(s) - vlookup two cells and return TRUE/FALSE for match?

[cowpers]

Hi there,

Obviously I'm new here and thought I'd ask some of the experts here for a bit of help. Hope someone can assist

Background: I'm trying to create a rec report, ideally on one page, for multiple criteria. The situation is we get a report on our trade positions (investment stuff) from an outside service we use. We also have a report internally from what we've booked. We're looking to tie out are basic things like price, original par, revised par, trade date, counter-party banks, etc. I've consolidated data from both sources on one page (DATA) where all data points are lined up (say EXTERNAL is from rows 1-500, and INTERNAL is 510-1010). Now I need to create a front page showing the result of each tie-out. The lookup value will be a combination of the fund name + trade ticket number which would almost guarantee a unique ID (fund name XYZ, ticket number 12345 = XYZ12345)

Current tabs on spreadsheet: DATA, EXTERNAL, INTERNAL. I need to add a REC page

Question: What formula can I use to check on each and return TRUE/FALSE? The goal is to have about 8 columns on the REC, each for one of the tie-outs (let's use price as an example.). I know the basic VLOOKUP to return one value, but it appears in this case I will need VLOOKUP to check two matching values based on unique ID XZY12345, compare the price, and return TRUE if they match, FALSE if they don't

Here's something I saw from one of our spreadsheets that I think does the same to confirm counter-party, though I have no idea how to read this lengthy formula.

=EXACT(VLOOKUP(IF(Q2="COMPANY",R2,Q2),FUNDS!$M$1:$N$400,2,FALSE),INDEX(PENDING!$G$4:$G$3218,MATCH(CP!C2,PENDING!$N$4:$N$3218,0)))

Thanks for your time reading and helping. I feel guilty not being a part of the community and asking for your help. I would offer to compensate but I'm sure that's against guidelines. I'm willing to donate $10 to Hope4paws or the Salvation Army or something! thanks again

 

[NBVC]

=INDEX(EXTERNAL!$C$2:$C$400,MATCH(1,INDEX((EXTERNAL!$A$2:$A$400=A2)*(EXTERNAL!$B$2:$B$400=B2),0),0))=INDEX(INTERNAL!$C$2:$C$400,MATCH(1,INDEX((INTERNAL!$A$2:$A$400=A2)*(INTERNAL!$B$2:$B$400=B2),0),0))

where Fund name, Trade Number and Price are in columns A:C respectively of the External and Internal sheets, and you list the Fund and Trade number in active sheet starting at A2 and B2, respectively.. The formula would go in C2, copied down.

or since you are looking up numbers (prices), you can use the simpler and more efficient SUMIFS

=SUMIFS(EXTERNAL!$C$2:$C$400,EXTERNAL!$A$2:$A$400,A2,EXTERNAL!$B$2:$B$400,B2)=SUMIFS(INTERNAL!$C$2:$C$400,INTERNAL!$A$2:$A$400,A2,INTERNAL!$B$2:$B$400,B2)


adjust ranges to suit.

 

[cowpers]

Hey - thanks for your quick response

I've combined both fund and ticket number to one column using &, so i now i have a column where ticket number + Fund is combined.

here is the DATA tab for example

EXTERNAL
VALUE	PRICE	SIDE
16002FR	99.00	Buy
16001CLO	99.00	Buy
15002FR	97.00	Buy
15001CLO	97.00	Buy
14000CDO	98.50	Sell
16000CDO	99.00	Sell
15000CDO	97.00	Buy
14002FR	98.50	Buy
14001CLO	98.50	Buy
INTERNAL
VALUE	PRICE
14000CDO	98.50	Sell
14001CLO	98.50	Buy
14002FR	98.50	Buy
15000CDO	97.00	Buy
15001CLO	97.00	Buy
15002FR	97.00	Buy
16000CDO	99.00	Sell
16001CLO	99.00	Buy
16002FR	99.00	Buy

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Here's the front REC tab with the same VALUE in column A. the goal is to look up the value in A and compare to both in DATA from Internal and External, and bring in TRUE/FALSE (i typed those out below to show)

VALUE	PRICE
14000CDO	compare value in A2 (14000CDO) and return TRUE/FALSE if price on both matches on DATA
14001CLO	TRUE
14002FR	FALSE
15000CDO	TRUE
15001CLO	FALSE
15002FR	TRUE
16000CDO	TRUE
16001CLO	TRUE
16002FR	TRUE
	^^^
	Something like this

<colgroup><col><col></colgroup><tbody>
</tbody>

the reason i don't use SUM (aside from me not understanding the formulas very well) is because some are not numbers. like counter-party/side. i'll also want to check to see if 14000CDO says SELL or BUY for both internal and external. same goes for BANK, which would include JP MORGAN, GOLDMAN SACHS and others

 

[NBVC]

Try:
=INDEX(EXTERNAL!$B:$B,MATCH(A2,EXTERNAL!$A$2:$A$400,0))=INDEX(INTERNAL!$B:$B,MATCH(A2,INTERNAL!$A:$A,0))

 

[cowpers]

Try:
=INDEX(EXTERNAL!$B:$B,MATCH(A2,EXTERNAL!$A$2:$A$400,0))=INDEX(INTERNAL!$B:$B,MATCH(A2,INTERNAL!$A:$A,0))

Not exactly working out yet. Not sure why the formula is referencing the internal and external tabs. I've consolidated both sets of data to a tab labeled "DATA", and it's pasted above where it says EXTERNAL and then INTERNAL. this is my way of organizing the data from both sources. they also won't be in the same order in terms of the VALUE column

from what i'm seeing, we're trying to look up the value in A2, locate the same in DATA, and see if price (column B in DATA) matches for both. i know it's a bit complicated, and this may not be the most efficient way to doing a tie-out. appreciate your assistance!

 

[NBVC]

I thought EXTERNAL and INTERNAL were separate sheets?

If they are in the same sheet, then the left side reference one table, the right side of the formula would reference the other table.

=INDEX(DATA!$B$2:$B500,MATCH(A2,DATA!$A$2:$A$500,0))=INDEX(DATA!$B$510:$B$1010,MATCH(A2,DATA!$A$510:$A$1010,0))

 

[cowpers]

They are but the reason I created a separate sheet to consolidate both is because from our EXTERNAL sheet, they have about 30 columns and in different order than what we have. In our INTERNAL, we only have about 12. I had to bring it all together, the values i'm looking for, on one sheet (i labeld it DATA)

the formula still isn't pulling in for me with the adjustments i'm making. (using a sample i made)

can i attach the spreadsheet here or something?

i really do appreciate your time!

 

[NBVC]

The only way to attach a sheet in this forum is through an outside source like Dropbox of similar free (no membership required) file hosting service and then provide a link here.

 

[cowpers]

does this link work? this is a rough draft of our real trade file, obviously

https://docs.google.com/file/d/0BwLZZcWGac6ANmZ6M1pXMVFLamM/edit?pli=1

front page is to bring in true/false based on matching for price, buy/sell side (will add others later - trade date, bank, par, etc.)

 

[NBVC]

Sorry, the link isn't opening for me....