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.

 

[sunnny]

Hello Peter Sir,

Your below mentioned formula is working wonderfully. But now I want little change in that. I tried but could not succeed.

=IF(COUNTIFS(A$4:A$24,D4,B$4:B$24,E4)+COUNTIFS(A$4:A$24,D4,B$4:B$24,E4-50),"YES","NO")


now I'm trying

=IF(COUNTIFS(A$4:A$24,D4,B$4:B$24,""),"YES","NO")


i.e.


A4 matches D4.

And if B4 is blank, then "No", otherwise "Yes".

For info, B4 contains text, which is result of another formula. Column B may contains text or may be blank as determines by the formula. But this column contains formula which has to be considered so that your formula does not show false "Yes".

Please help.

 

[Aladin Akyurek]

I believe IsNumber/Match performs better than CountIif(s). Also, what change/modification you desire to make vis-a-vis the earier specs you provided is not clear. Would you clarify, perhaps with a small sample>

 

[Peter_SSs]

now I'm trying

=IF(COUNTIFS(A$4:A$24,D4,B$4:B$24,""),"YES","NO")


i.e.


A4 matches D4.

And if B4 is blank, then "No", otherwise "Yes".

I, too, would like to see some sample data and expected results.
Further, your attempted formula above and the written description appear to be contradictory about what should be "Yes" and what should be "No" so careful clarification of that would also be useful.

 

[sunnny]

Hello Sir,

The example is as below :

	A	B	C	D	E	F	G
1	Table 1			Table 2
2
3	CASE	NOTICE		CASE
4	C-70265	C18 (Ad		C-10080	NO
5	C-5844			C-70265	YES
6	D-7902	D18		C-10180	NO
7	C-10080			D-5080	YES
8	C-10180			C-5844	NO
9	D-5080	SO 85B		D-7902	YES

<tbody>
</tbody>

If case as in column A & column D matches, and column B shows text (which is result of vlookup formula), column E should show value "Yes" otherwise "No".

Hope this clarifies.

 

[Aladin Akyurek]

[...]

If case as in column A & column D matches, and column B shows text (which is result of vlookup formula), column E should show value "Yes" otherwise "No".
[...]

Whichever is appropriate...

Either...

E4, just enter & copy down:

=IF(LEN(INDEX($B$4:$B$9,MATCH(D4,$A$4:$A$9,0))),"YES","NO")

Or...

E4, just enter & copy down:
=IF(LEN(T(INDEX($B$4:$B$9,MATCH(D4,$A$4:$A$9,0)))),"YES","NO")

 

[Peter_SSs]

If the list of items in column A is unique then I think Aladin's formulas meet your needs.
However, if the column A items are not unique, as was the case with your earlier sample data in this thread, then the question becomes "What do you want returned if sometimes the item you want matched has text in column B and sometimes not?"

The formula in column E below returns "YES" if any of the matching items has text in column B.
The formula in column F below returns "YES" only if all the matching items have text in column B.

Excel Workbook
	A	B	C	D	E	F
3	CASE	NOTICE		CASE
4	C-70265	C18 (Ad		C-10080	YES	NO
5	C-5844			C-70265	YES	YES
6	C-10080	K32		C-10180	NO	NO
7	D-7902			D-5080	YES	YES
8	C-10080			C-5844	NO	NO
9	C-10180			D-7902	YES	NO
10	D-7902	D18
11	C-70265	ABC
12	D-5080	SO 85B
13	C-10080	xxx
14
Check Matches

 

[sunnny]

Thanks both of you, Aladin & Peter Sir,

All the formulae are working fine.

Thank you very much.

 

[Peter_SSs]

All the formulae are working fine.

Depending on your circumstances and requirements, they don't all return the same results.