A difficult Lookup

ayazgreat

Well-known Member
Hi All, I have got below table and I need a formula to search values as per from col h to k, let me explain you in col H row no.4 In = 1 and Out = 2, in col I Request = 1 and Quantity = 2, in col J values are in col A and Col K values are in row 2 from col c to f. what formula should I put in cell I12 to get result by looking up values in h4, i4, j8, and k6 as shown below highlighted result with accurate in values in col I 12. I mean to say here look up each value in its given table like In request table or In Quantity or Out Request or out Quantity Sheet1

 A B C D E F G H I J K 1 In Request 2 1 2 3 4 In Request SD P 3 Product P I K M Out Quantity JZ I 4 1 SD 1000 1584 2236 26 1 2 SS K 5 2 JZ 94 220 382 27 WM L 6 3 SS 744 326 928 20 AS 3 7 4 WM 68 7 PS 8 5 AS 1 2 9 6 PS 55 10 11 In Quantity 12 1 2 3 4 Result 71,010 13 Product P I K M 14 1 SD 2986755 4965095 6244840 15524000 15 2 JZ 25523 38868 71010 133442 16 3 SS 152477 100445 346908 969013 17 4 WM 2104 1206 18 5 AS 5 19 6 PS 514 20 21 Out Request 22 1 2 3 4 23 Product P I K M 24 1 SD 8 8 8 3 25 2 JZ 9 18 99 12 26 3 SS 13 5 6 12 27 4 WM 12 1 6 28 5 AS 7 29 6 PS 22 30 31 Out Quantity 32 1 2 3 4 33 Product P I K M 34 1 SD 2021800 4487000 6021800 12110000 35 2 JZ 41173 37035 55152 1396 36 3 SS 260722 108994 552236 53083 37 4 WM 1329 1 1205 38 5 AS 1088 39 6 PS 579

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Assuming "In Request", "In Quantity" etc is actually in the column B cell in that row (and merged or centered across to column F) then try

=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)

Assuming "In Request", "In Quantity" etc is actually in the column B cell in that row (and merged or centered across to column F) then try

=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)
Peter I don't have words to say but thank you very much, you are so intelligent and expert.

Actually, given the uniform spacing of your tables, I think this should work too.

=INDEX(C4:F39,(H4-1)*20+(I4-1)*10+J8,K6)

Actually, given the uniform spacing of your tables, I think this should work too.

=INDEX(C4:F39,(H4-1)*20+(I4-1)*10+J8,K6)
Great, it also works but I don't understand *20 and *10

Peter I don't have words to say but thank you very much, you are so intelligent and expert.
Thank you for those very kind words.

Great, it also works but I don't understand *20 and *10
The 20 is because the "Request" tables are exactly 20 rows apart.
The 10 is because each "Quantity" table is exactly 10 rows below the corresponding "Request" table.

Thanks for valuable knowledge

Dear Peter I have made some changes data and added months in first row so now criteria is to match month also in your formula with same others conditions I would like you to please change in your first provided formula.
 B C D E F G H I J K L M N O 1 Jan-13 Feb-13 2 In Request 3 1 2 3 4 1 2 3 4 In Request SD P 4 Product P I K M P I K M Out Quantity JZ I 5 SD 1000 1584 2236 26 10 5 9 2 1 2 SS K 6 JZ 94 220 382 27 41 38 62 19 WM L 7 SS 744 326 928 20 6 1 8 20 AS 3 8 WM 68 0 0 7 11 0 0 10 PS 9 AS 0 0 0 1 0 0 0 11 2 10 PS 0 0 0 55 0 0 0 25 11 12 In Quantity 13 1 2 3 4 1 2 3 4 Feb-13 106670 14 Product P I K M P I K M 15 SD 2986755 4965095 6244840 15524000 2036720 4353000 5357900 4296800 16 JZ 25523 38868 71010 133442 73634 38089 106670 114416 17 SS 152477 100445 346908 969013 210168 116567 453974 232574 18 WM 2104 0 0 1206 1565 0 0 1566 19 AS 0 0 0 5 0 0 0 1043 20 PS 0 0 0 514 0 0 0 1631 21 22 Out Request

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Assuming those values are real dates and that they are in columns C & G and that the dates are all in the same year, try

=INDEX(C5:J40,(L5-1)*20+(M5-1)*10+N9,(MONTH(L13)-1)*4+O7)

Peter I would be very thankful to you if you please changes of the same in below formula also.=INDEX(C1:F39,MATCH(INDEX(H2:H3,H4)&" "&INDEX(I2:I3,I4),B1:B39,0)+2+J8,K6)

Replies
1
Views
110
Replies
0
Views
134
Replies
2
Views
51
Replies
8
Views
245
Replies
1
Views
498

1,196,497
Messages
6,015,554
Members
441,900
Latest member
Inaschemitex2023

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.

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