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

 

[jasonb75]

It works no different to your formula if you insert above the first row of data in that it will not include the inserted rows in the calculations.

If you insert rows above the headers then it would fail but I would say that is a bit of an unusual practice.

Normally I would use the entire column for the INDEX range, I just reduced it this time so that the results table was not in the precedent range although it will work fine either way.

 

[Toadstool]

@jasonb75 and @Fluff , you both make excellent points (and we have yet to hear from the OP if any satisfy their requirement) but I believe you are both missing the the most important aspect of this thread.
Toadstool posted an Excel 2013 solution first!

 

[sanrama]

@jasonb75
B13 formula is not working but C13 formula is working, seems I am making some mistake in B13 formula

@Fluff @Toadstool
Your formula is also working nicely

Thanks guys for providing the solution

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[sanrama]

@Fluff @Toadstool @jasonb75
Is there any solution for dragging formula horizontal instead of vertical ?
Horizontal = B13, C13, D13, E13,.... ( keeping raw 13 constant )
Vertical = B13, B14, B15, B16,....( Keeping column B constant )

Thanks in advance

 

[Toadstool]

Sanrama,

I'm not sure if I'm following correctly but do you mean like 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	Lookup 1	Lookup 2
10	2	B	Mr. A	Mr. D
11	6	L	Mr. B	Mr. D	Mr. E
12	8	G	Mr. D	Mr. E
13	5	G	Mr. D	Mr. E
14
Sheet1 (2)

Cell Formulas
Range	Formula
C10:I14	C10	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)-ROW($A$2)/(($B$1:$I$1=$A10)*($B$3:$I$7=$B10)),COLUMN()-COLUMN($B$10))),"")

 

[sanrama]

@Toadstool
This is what exactly I am asking, formula is working nicely

Thank you

 

[sanrama]

Hi @Fluff

Your formula is satisfying my requirement as I have inserted some raw above the first row of my datasheet.
Can we convert your formula to horizontal drag instead of vertical drag formula like Toadstook suggested.

Thanks

 

[Fluff]

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	Lookup 1	Lookup 2
10	2	B	Mr. A	Mr. D
11	6	L	Mr. B	Mr. D	Mr. E
12	8	G	Mr. D	Mr. E
13	5	G	Mr. D	Mr. E
14
Data

Cell Formulas
Range	Formula
C10:I13	D10	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,(ROW($B$3:$I$7)-ROW($B$3)+1)/($B$1:$I$1=$A10)/($B$3:$I$7=$B10),COLUMNS($B$10:C10))),"")
D14:I14	D14	=IFERROR(INDEX($A$3:$A$7,AGGREGATE(15,6,ROW($A$3:$A$7)-ROW($A$2)/(($B$1:$I$1=$A14)*($B$3:$I$7=$B14)),COLUMN()-COLUMN($B$10))),"")

 

[sanrama]

@Fluff

Thanks, perfectly working