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.

 

[Aladin Akyurek]

Select the range housing Table 1 and name the selection as LTable.

Let A:B, Sheet2, house Table 2, the headers included.

Sheet2, C2, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(A2,IF((INDEX(LTable,0,2)=B2)+(INDEX(LTable,0,2)=B2+50),INDEX(LTable,0,1)),0))

 

[sunnny]

Thanks for the efforts.

The formula is working but not the way I want. Let me again explain :

Criteria

1. code of table 2 = code of Table 1

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

3. If condition above 1 & 2 are true, "YES", otherwise "NO"

Please make appropriate amendments in formula.

Thanks in advance.

 

[Aladin Akyurek]

Did you apply control+shift+enter to the formula?

 

[Peter_SSs]

Your Table 2 has some strange 'numbers' (highlighted green in my screen shot) but see if these standard entry formula do what you want.
If the green cells are replaced with proper numbers then the SUMPRODUCT formula should return YES or NO in those rows too.
Adjust ranges to suit then copy formulas down.

Excel Workbook
	A	B	C	D	E	F	G
1	Table 1			Table 2
2
3	CODE	AMOUNT		CODE	AMOUNT
4	70265	10,469		26351	257	NO	NO
5	5844	997		70741	1,943.00	NO	NO
6	5844	416		71218	1,018.00	NO	NO
7	10080			46935	1,02,854.00	NO	#VALUE!
8	10080			71406	7,867.00	NO	NO
9	10080			26979	228	NO	NO
10	10600	3,132		46318	4,438.00	NO	NO
11	11100			47180	101	NO	NO
12	11102	4,933		47180	131	NO	NO
13	11102			47180	199	NO	NO
14	11102	808		12285	424	NO	NO
15	11102	920		39102	303	NO	NO
16	11249	24,817		72099	671	YES	YES
17	11447			72099	11,243.00	NO	NO
18	73803	47407		70531	40,352.00	NO	NO
19	11447			26373	2,13,783.00	NO	#VALUE!
20	11504			70244	16,889.00	NO	NO
21	11504			42385	3,419.00	NO	NO
22	11127	11,394		73803	47,457.00	NO	NO
23	11510	1,536		72861	7,892.00	YES	YES
24	11510	741		26655	834	NO	NO
25	11510	3,199
26	11510	301
27	47180	149
28	11529	531
29	11529	441
30	11529	746
31	11529	723
32	11562	559
33	11562	815
34	11579
35	72099	671
36	11579
37	11627
38	72861	7892
39	11651	2,812
40
Check Matches

 

[sunnny]

There should not be 2 formulae. Formula is one which should have These criteria :

Criteria

1. code of Table 1 = code of table 2

AND

2. Amount of table 1 = Either Amount of Table 2 OR Amount of Table 2 + 50

3. If condition above 1 & 2 are true, "YES", otherwise "NO"

And these conditions should be fulfilled in one formula only.

The strange numbers are nothing but numbers in format of 0,00,000.00 instead of 000,000.00

To Mr. Aladin : Yes Sir, I applied control + shift + enter. The formula was fine but did not work with my criteria as I've given.

 

[Aladin Akyurek]

There should not be 2 formulae. Formula is one which should have These criteria :

Criteria

1. code of Table 1 = code of table 2

AND

2. Amount of table 1 = Either Amount of Table 2 OR Amount of Table 2 + 50

3. If condition above 1 & 2 are true, "YES", otherwise "NO"

And these conditions should be fulfilled in one formula only.

The strange numbers are nothing but numbers in format of 0,00,000.00 instead of 000,000.00

To Mr. Aladin : Yes Sir, I applied control + shift + enter. The formula was fine but did not work with my criteria as I've given.

Would you post the desired results for Table 2 given Table 1 and the conditions you stipulate?

 

[Peter_SSs]

There should not be 2 formulae.

My two suggested formulas were to give you a CHOICE of one OR the other. You didn't state what Excel version you are using and the COUNTIFS formula is slightly more efficient but will only work in Excel 2007 or later, whilst the slightly less efficient SUMPRODUCT is applicable to all Excel versions.

Formula is one which should have These criteria :

Criteria

1. code of Table 1 = code of table 2

AND

2. Amount of table 1 = Either Amount of Table 2 OR Amount of Table 2 + 50

3. If condition above 1 & 2 are true, "YES", otherwise "NO"

I believe that is exactly what each of my formulas does. If not please advise which of the cells in my screen shot are incorrect answers for the sample data used and explain why they are wrong and/or provide the desired results as Aladin has requested.

The strange numbers are nothing but numbers in format of 0,00,000.00 instead of 000,000.00

OK, thanks for clarifying. Unfortunately when I copied them from your post to my worksheet, they come with the comma separators so don't Paste as numbers. I can manually fix that but it doesn't alter my suggested formulas.

 

[sunnny]

Here is the table with suggested result :

Table 1 Table 2

CODE	AMOUNT		CODE	AMOUNT	RESULT
70265	10469		26274	185	NO
5844	997		26351	257	NO
5844	416		26539	1312	NO
10080			70741	1943	NO
10080			71215	179	NO
10080			71215	540	NO
10600	3132		71218	1018	NO
11100			46935	102854	NO
11102	4933		71030	17850	NO
11102			71406	7867	NO
11102	808		71208	2878	NO
11102	920		26979	228	NO
11249	24817		46318	4438	NO
47180	101		47180	151	Yes
47180	131		47180	131	Yes
47180	199		47180	199	Yes
12185	1424		12185	1474	Yes
12285	424		12285	424	Yes
39102	303		39102	353	Yes
42166	323		42166	323	Yes
44234	4241		44234	4241	Yes

<tbody>
</tbody>

As you may see, for the code 47180 Amount in Table 1 is 101 while in Table 2, it is 151 (101 + 50) and for code 12185 amount is 1424 in both tables but still the result is showing "Yes" in both cases.

If code & amount in both table did not match, it is showing "No".

 

[Aladin Akyurek]

Here is the table with suggested result :

Table 1 Table 2

CODE	AMOUNT		CODE	AMOUNT	RESULT
70265	10469		26274	185	NO
5844	997		26351	257	NO
5844	416		26539	1312	NO
10080			70741	1943	NO
10080			71215	179	NO
10080			71215	540	NO
10600	3132		71218	1018	NO
11100			46935	102854	NO
11102	4933		71030	17850	NO
11102			71406	7867	NO
11102	808		71208	2878	NO
11102	920		26979	228	NO
11249	24817		46318	4438	NO
47180	101		47180	151	Yes
47180	131		47180	131	Yes
47180	199		47180	199	Yes
12185	1424		12185	1474	Yes
12285	424		12285	424	Yes
39102	303		39102	353	Yes
42166	323		42166	323	Yes
44234	4241		44234	4241	Yes

<TBODY>
</TBODY>

As you may see, for the code 47180 Amount in Table 1 is 101 while in Table 2, it is 151 (101 + 50) and for code 12185 amount is 1424 in both tables but still the result is showing "Yes" in both cases.

If code & amount in both table did not match, it is showing "No".

A2:B22 is named as LTable.

F2, control+shift+enter, not just enter, and copy down:

=ISNUMBER(MATCH(D2,IF((INDEX(LTable,0,2)=E2)+(INDEX(LTable,0,2)=E2-50),
  INDEX(LTable,0,1)),0))+0

A 1 as result stands for a hit, a 0 for a miss.

See the workbook that implements this formula:
https://dl.dropboxusercontent.com/u/65698317/sunnny conditional match with index.xlsx