Two way lookup with if/match return

[sanrama]

Hi All,

Need help, I am struggling to find the solution. Below is my details,

		A	B	C	D	E	F	G	H	I	J
1		Name	Date	1	2	3	4	5	6	7	8
2		Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3		Mr. A	B	B	B	A	A	O	B	A
4		Mr. B	A	A	O	B	B	L	O	B
5		Mr. C	L	O	L	L	L	B	A	A
6		Mr. D	G	B	O	G	G	L	G	G
7		Mr. E	G	G	A	O	G	L	G	G

Case 1
Lookup 1	2	Date ( in raw 1 )
Lookup 2	B	Column F ( Lookup 1 matching column, in this case it matching column array is G3:G7 )
Result 1	Mr. A
Result 2	Mr. D
Result 3
Result 4

Case 2
Lookup 1	6	Date ( in raw 1 )
Lookup 2	L	Column F ( Lookup 1 matching column, in this case it matching column array is K3:K7 )
Result 1	Mr. B
Result 2	Mr. D
Result 3	Mr. E
Result 4

Thanks in advance

 

[Toadstool]

Hi Sanrama,

I'm a little confused as a date of 2 and a lookup of B suggests I should look in column D where I'd only find Mr. A.

For case 2 I'd look in column H and find no entries for L.

 

[jasonb75]

It is a very bad example, but I think that this is what is required.

Manpower plan.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Name	1	2	3	4	5	6	7	8
2	Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	Mr. A	B	B	B	A	A	O	B	A
4	Mr. B	A	A	O	B	B	L	O	B
5	Mr. C	L	O	L	L	L	B	A	A
6	Mr. D	G	B	O	G	G	L	G	G
7	Mr. E	G	G	A	O	G	L	G	G
8
9	Case 1
10	Lookup 1	2
11	Lookup 2	B
12
13	Result 1	Mr. A
14	Result 2	Mr. D
15	Result 3
16	Result 4
Sheet5

Cell Formulas
Range	Formula
B13:B14	B13	=FILTER(A3:A7,INDEX(B3:I7,0,MATCH(B10,B1:I1,0))=B11,"")
Dynamic array formulas.

 

[sanrama]

Hi Toadstool, Hi jasonb75,

Extremely sorry for incorrect data,
Please find below screenshot for corrected data

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Have you tried the formula that Jason supplied in post#3?

 

[sanrama]

Hi Fluff / Jeson,

Currently I am using Excel 2013, so filter formula is not available in the version.

 

[Toadstool]

Hi Sanrama,

Try this:

Sanrama.xlsx
	A	B	C	D	E	F	G	H	I
1	Name	1	2	3	4	5	6	7	8
2	Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	Mr. A	B	B	B	A	A	O	B	A
4	Mr. B	A	A	O	B	B	L	O	B
5	Mr. C	L	O	L	L	L	B	A	A
6	Mr. D	G	B	O	G	G	L	G	G
7	Mr. E	G	G	A	O	G	L	G	G
8
9	Case 1
10	Lookup 1	2
11	Lookup 2	B
12
13	Result 1	Mr. A
14	Result 2	Mr. D
15	Result 3
16	Result 4
17
18	Case 1
19	Lookup 1	6
20	Lookup 2	L
21
22	Result 1	Mr. B
23	Result 2	Mr. D
24	Result 3	Mr. E
25	Result 4
Sheet1

Cell Formulas
Range	Formula
B13:B16	B13	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)-ROW($A$2)/(($B$1:$I$1=$B$10)*($B$3:$I$7=$B$11)),ROW()-ROW($B$12))),"")
B22:B25	B22	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)-ROW($A$2)/(($B$1:$I$1=$B$19)*($B$3:$I$7=$B$20)),ROW()-ROW($B$21))),"")

 

[Fluff]

Thanks for updating your profile, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	1	2	3	4	5	6	7	8
2	Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	Mr. A	B	B	B	A	A	O	B	A
4	Mr. B	A	A	O	B	B	L	O	B
5	Mr. C	L	O	L	L	L	B	A	A
6	Mr. D	G	B	O	G	G	L	G	G
7	Mr. E	G	G	A	O	G	L	G	G
8
9	Case 1
10	Lookup 1	2
11	Lookup 2	B
12
13	Result 1	Mr. A
14	Result 2	Mr. D
15	Result 3
16	Result 4
17
Data

Cell Formulas
Range	Formula
B13:B16	B13	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,(ROW($B$3:$I$7)-ROW($B$3)+1)/($B$1:$I$1=$B$10)/($B$3:$I$7=$B$11),ROWS(B$13:B13))),"")

 

[jasonb75]

2 more methods that would be slightly more efficient. This is trivial with small data sets like your example, but with larger data sets it can be quite noticeable, especially on lower powered computers.
The formula in B13 is almost identical to the suggestions above, but by starting the index range in row 1 instead of row 3 it is possible to reduce the number of calculation steps in the aggregate array.

The formula in C13 takes it one step further by eliminating the date check from the array but this will only work if the dates in row 1 are always consecutive and starting from the 1st.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Name	1	2	3	4	5	6	7	8
2	Day	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
3	Mr. A	B	B	B	A	A	O	B	A
4	Mr. B	A	A	O	B	B	L	O	B
5	Mr. C	L	O	L	L	L	B	A	A
6	Mr. D	G	B	O	G	G	L	G	G
7	Mr. E	G	G	A	O	G	L	G	G
8
9	Case 1
10	Lookup 1	2
11	Lookup 2	B
12
13	Result 1	Mr. A	Mr. A
14	Result 2	Mr. D	Mr. D
15	Result 3
16	Result 4
Sheet8

Cell Formulas
Range	Formula
B13:B16	B13	=IFERROR(INDEX($A$1:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)/($B$3:$I$7=$B$11)/($B$1:$I$1=$B$10),ROWS(B$13:B13))),"")
C13:C16	C13	=IFERROR(INDEX($A$1:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)/(INDEX($B$3:$I$7,,$B$10)=$B$11),ROWS(C$13:C13))),"")

 

[Fluff]

The formula in B13 is almost identical to the suggestions above, but by starting the index range in row 1 instead of row 3 it is possible to reduce the number of calculation steps in the aggregate array.

Except that if rows are inserted above the first row of data, the formula will fail.