Lookup in 3 tables

Closed
Tornado1981 - Apr 1, 2010 at 02:32 PM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 1, 2010 at 10:52 PM
Hello,
I have a worksheet that contains 3 tables
Each table consists of 2 columns
A B
Name Code
AAA 300
BBB 301
CCC 302

D E
Name Code
XXX 303
YYY 304
ZZZ 305

G H
Date Code
1/1/2010 300
2/2/2010 301
3/3/2010 305
4/4/2010 300
5/5/2010 304
6/6/2010 301
7/7/2010 300


what i want to do is that when i enter a code (let's say 300) in the cell J1, then in cells K1:K3 appear the dates corresponding to the codes 300,301 & 302.
and if i enter 305, then in cells K1:K3 appear the dates corresponding to the codes 303,304 & 305.

Is that possible ?
Thanks


1 reply

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Apr 1, 2010 at 10:52 PM
some clarity is required

column J column k

300 1/1/10----formula is =INDEX($G$2:$G$8,MATCH($J$1+ROW(A1)-1,$H$2:$H$8,0),1)---copy this down for next 2 rows.
2/2/10
#N/A

305 3/3/10---the formula is =INDEX($G$2:$G$8,MATCH($J$5-ROW(A1)+1,$H$2:$H$8,0),1)---copy it down
5/5/10
#N/A


in column H there is no 302 and no 303 and that is why #N/A

there are moe than 301 or 300 in column H which date do youwnat.

if you modify the formlas to suity you it is ok;.
0