LOOKING up data ( stuck on project)

[demodren]

All, I am working on finishing my project and stuck on last part.. Could someone please help me point me in the right direction, I would really appreciate it.

I have 2 spreadsheets:
1) data 1 (has 3 columns: account, id, profits)
2) data 2 (has 3 columns: account, id, profits)

I use LOOKUP function in data1 to find profits in data 2 by identifiers: account and id..

this is where I am stuck: How can I bring any profits into data 1 from data 2, that are NOT in data 1(this I would assume would be possible with lookup also by matching ACCOUNT and ID)

I lookup the profits by 2 identifiers: ACCOUNT(there are 5 different accounts) and ID(is unique, but I can have 5 same IDS across 5 different accounts)

I have been looking for solution on this for some time.. Please any advice is very appreciated

thanks so much!

formula that I use in data1 to pull profits from data2

=LOOKUP(9^99,IF({1,0},0,LOOKUP(2,1/(A3=profits!A$3:A$100)/(C3=profits!B$3:B$100),profits!E$3:E$100)))

 

[Aladin Akyurek]

Assuming that you want to fetch profits from Sheet2 into Sheet1...

Control+shift+enter, not just enter:

Sheet1, C2

=INDEX(Sheet2!$C$2:$C$100,MATCH(1,IF(Sheet2!$A$2:$A$100=A2,IF($B$2:$B$100=B2,1)),0))

 

[demodren]

Thanks Aladin. I need to fetch fetch ALL profits from Sheet2 into Sheet1.
Sheet 1 already has some "Account" and "ID" (so I fetch profits by pulling it from Sheet 2 based on those 2 identifiers) I am ok with that part

the part I am stuck on is. I now need to fetch anything remaining.. for example..

sheet 1 - 50 rows(profits per combination of account and ID)
sheet 2 - 300 rows(i match profits from sheet 2 to sheet 1 by account and ID and now I need to bring remainign 250 profits which have different combination of account and ID)

 

[demodren]

Hi all, any thoughts how to approach this problem? Any input would be much appreciated.

Thanks so much
Dan

 

[demodren]

sorry 1 small correction.. this is in between 2 tabs.. not 2 spreadsheets..

Please if anyone has any suggestions would be very much appreciated. I am totally stuck on this part....

thanks so mcuh

 

[demodren]

Hi Aladin, was wondering if you had any thoughs around this? was hoping you had some suggestions.. totally hitting brick wall here...

 

[Aladin Akyurek]

Hi Aladin, was wondering if you had any thoughs around this? was hoping you had some suggestions.. totally hitting brick wall here...

Could you post a small sample along the desired results?