# Lookup in another table with criteria

#### sunnny

##### New Member
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.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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))

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.

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

Last edited:
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
ABCDEFG
1Table 1Table 2
2
3CODEAMOUNTCODEAMOUNT
47026510,46926351257NONO
55844997707411,943.00NONO
65844416712181,018.00NONO
710080469351,02,854.00NO#VALUE!
810080714067,867.00NONO
91008026979228NONO
10106003,132463184,438.00NONO
111110047180101NONO
12111024,93347180131NONO
131110247180199NONO
141110280812285424NONO
151110292039102303NONO
161124924,81772099671YESYES
17114477209911,243.00NONO
1873803474077053140,352.00NONO
1911447263732,13,783.00NO#VALUE!
20115047024416,889.00NONO
2111504423853,419.00NONO
221112711,3947380347,457.00NONO
23115101,536728617,892.00YESYES
241151074126655834NONO
25115103,199
2611510301
2747180149
2811529531
2911529441
3011529746
3111529723
3211562559
3311562815
3411579
3572099671
3611579
3711627
38728617892
39116512,812
40
Check Matches

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.

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?

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.

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".

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:
Rich (BB code):
``````=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

1,196,260
Messages
6,014,293
Members
441,813
Latest member
elevate

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back