Lookup in another table with criteria

[sunnny]

Hi

I am sorry for my earlier post of same heading, as this contains texts in machine language. I am posting my query again as below :

I have data across thousands of rows in Table 1 and want to match it with data in table 2 with some criteria.

Table 1

CODE	AMOUNT
70265	10,469
5844	997
5844	416
10080
10080
10080
10600	3,132
11100
11102	4,933
11102
11102	808
11102	920
11249	24,817
11447
73803	47407
11447
11504
11504
11127	11,394
11510	1,536
11510	741
11510	3,199
11510	301
47180	149
11529	531
11529	441
11529	746
11529	723
11562	559
11562	815
11579
72099	671
11579
11627
72861	7892
11651	2,812

<tbody>
</tbody>

Table 2

CODE	AMOUNT
26351	257.00
70741	1,943.00
71218	1,018.00
46935	1,02,854.00
71406	7,867.00
26979	228.00
46318	4,438.00
47180	101.00
47180	131.00
47180	199.00
12285	424.00
39102	303.00
72099	671.00
72099	11,243.00
70531	40,352.00
26373	2,13,783.00
70244	16,889.00
42385	3,419.00
73803	47,457.00
72861	7,892.00
26655	834.00

<tbody>
</tbody>

I want that if code & amount of table 2 (either amount as mentioned or amount + 50) matches with code & amount of Table 1, it should indicate in another column of table 2.

Please try to help me out.

Thank you.

 

[Peter_SSs]

Just noting that the reason the previous suggestions did not meet your needs is that you have now swapped your requirement around.

Originally you stated it as ..

2. ...OR ((Amount of Table 2 + 50) = Amount of Table 1)

.. but now you are stating that Amount of Table 1 + 50 = Amount of Table 2


The corresponding adjustment to my previous choice of formulas are shown below.

Excel Workbook
	A	B	C	D	E	F	G
1	Table 1			Table 2
2
3	CODE	AMOUNT		CODE	AMOUNT	RESULT	RESULT
4	70265	10469		26274	185	NO	NO
5	5844	997		26351	257	NO	NO
6	5844	416		26539	1312	NO	NO
7	10080			70741	1943	NO	NO
8	10080			71215	179	NO	NO
9	10080			71215	540	NO	NO
10	10600	3132		71218	1018	NO	NO
11	11100			46935	102854	NO	NO
12	11102	4933		71030	17850	NO	NO
13	11102			71406	7867	NO	NO
14	11102	808		71208	2878	NO	NO
15	11102	920		26979	228	NO	NO
16	11249	24817		46318	4438	NO	NO
17	47180	101		47180	151	YES	YES
18	47180	131		47180	131	YES	YES
19	47180	199		47180	199	YES	YES
20	12185	1424		12185	1474	YES	YES
21	12285	424		12285	424	YES	YES
22	39102	303		39102	353	YES	YES
23	42166	323		42166	323	YES	YES
24	44234	4241		44234	4241	YES	YES
Check Matches

 

[sunnny]

Thanks a lot, Mr Aladin. The formula is working fine now. If any problem occurs, I'll again trouble you.

Thanks once again.

 

[sunnny]

Thanks Mr Aladin. The formula is working fine now. If any problem occurs, I'll trouble you again.

Once again thanks a lot.

 

[Aladin Akyurek]

Thanks Mr Aladin. The formula is working fine now. If any problem occurs, I'll trouble you again.

Once again thanks a lot.

You are welcome.

 

[Peter_SSs]

Glad to see you have a working solution, but a pity that a formula that is physically shorter, uses considerably less function calls & doesn't require CSE entry doesn't even seem to have been tried.

 

[sunnny]

Hello Peter_SSs Sir,

Your formula is also working fine but may be I am somewhere wrong. When I make appropriate changes in cell ranges, output is "Value".

Please find below the changes I've made :

=IF(COUNTIFS(A$2:A$3610,D2,B$2:B$56,E2)+COUNTIFS(A$2:A$3610,D2,B$2:B$56,E2-50),"YES","NO")

These changes I made after copying the appropriate columns to same sheet, otherwise column A & B are on a different sheet among multiple other columns and the same is the case with columns D & E.

I'll again try with this formula as this is easy & simple formula .

 

[Peter_SSs]

Hello Peter_SSs Sir,

Your formula is also working fine but may be I am somewhere wrong. When I make appropriate changes in cell ranges, output is "Value".

Please find below the changes I've made :

=IF(COUNTIFS(A$2:A$3610,D2,B$2:B$56,E2)+COUNTIFS(A$2:A$3610,D2,B$2:B$56,E2-50),"YES","NO")

These changes I made after copying the appropriate columns to same sheet, otherwise column A & B are on a different sheet among multiple other columns and the same is the case with columns D & E.

I'll again try with this formula as this is easy & simple formula .

Your Table 1 either goes to row 3610 or to row 56, so the red and blue numbers should match, as they did in my posted formulas.
In post #1 you said you had thousands of rows so I assume it is row 3610 we are interested in. Therefore try

=IF(COUNTIFS(A$2:A$3610,D2,B$2:B$3610,E2)+COUNTIFS(A$2:A$3610,D2,B$2:B$3610,E2-50),"YES","NO")

 

[sunnny]

Thanks a lot perter Sir,

The formula is working fine now.

Thanks a lot again for the efforts.

 

[sunnny]

Thanks a lot Peter Sir,

The formula is working fine now.

Thanks a lot again for the efforts.

 

[Peter_SSs]

Thanks a lot Peter Sir,

The formula is working fine now.

Thanks a lot again for the efforts.

Glad to help. Thanks for letting us know.