Double VLOOKUP maybe needs a array formula

[Fin Fang Foom]

Good Afternoon,

I'm trying to do a double Lookup . But the values are not in one column it could be coulmn C thru AF.

So I have this formula and it gives me a #N/A error:

=INDEX(Sheet1!$C$3:$AF$24,MATCH(1,INDEX((Sheet1!$B$3:$B$24=Sheet2!$B$2)*(Sheet1!$C$2=Sheet2!G5),0,1),0))
In G5 it should say 147.50%

And H5 should be 132.31%

 

[Aladin Akyurek]

Sheet1!$C$1:$AF$1 ---> Sheet1!$C$2:$AF$2

 

[Fin Fang Foom]

Hi Aladin,


I did what you suggested now I'm getting a !REF# error.

This Formula is in G5:

=INDEX(Sheet1!$C$3:$AF$24,MATCH(1,INDEX((Sheet1!$B$3:$B$24=Sheet2!$B$2)*(Sheet1!$C$2:$AF$2=Sheet2!G5),0,1),0))

Calendar array.xls
	B	C	D	E	F	G	H	I	J
1
2	Antonio Buenrostro
3		September 2005
4		Sunday	Monday	Tuesday	Wednesday	Thursday	Fridy	Saturday
5						09-01-2005	09-02-2005	09-03-2005
6		#N/A	#N/A	#N/A	#N/A	#REF!	#N/A	#N/A
Sheet2

 

[Yogi Anand]

Hi Fin Fang Foom:

If I understand you correctly, you need the following formula in cell G5 ...

=INDEX(Sheet1!$C$2:$G$15,MATCH($B$2,Sheet1!$B$2:$B$15,0),MATCH(G$5,Sheet1!$C$2:$G$2,0))

then copy this to cell H6, ...

as presented in the following:

Book1
	A	B	C	D	E	F	G	H	I
1
2		Antonio Buenrostro
3
4			Sunday	Monday	Tuesday	Wednesday	Thursday	Fridy	Saturday
5							09/01/2005	09/02/2005	09/03/2005
6							147.50%	132.31%
7
Sheet2

 

[Fin Fang Foom]

Thank You ,Yogi Anand


The formula is perfect. Now I understand my ranges were reverse.

The formula is perfect.

 

[Aladin Akyurek]

Hi Aladin,


I did what you suggested now I'm getting a !REF# error.

This Formula is in G5:

=INDEX(Sheet1!$C$3:$AF$24,MATCH(1,INDEX((Sheet1!$B$3:$B$24=Sheet2!$B$2)*(Sheet1!$C$2:$AF$2=Sheet2!G5),0,1),0))

...

I didn't pay attention to the whole formula. You don't need an array formula for your retieval task: Rather, just an INDEX formula or a VLOOKUP formula:

=INDEX(Sheet1!$C$3:$AF$24,MATCH(B2,Sheet1!$B$3:$B$24,0),MATCH(G5,Sheet1!$C$2:$AF$2,0))

=VLOOKUP(B2,Sheet1!$B$3:$AF$24,MATCH(G5,Sheet1!$B$2:$AF$2,0),0)

BTW, it's better not to put up non-working formulas to describe the problem you want to solve. Instead just describe it along with the expected/desired result.

 

[Fin Fang Foom]

Thanks Aladin it works just fine.


Now I have another problem How to get the date that matches with the days of the week.

Look at the example below:

Calendar array.xls
	A	B	C	D	E	F	G	H	I
1	09-01-2005	Antonio Buenrostro
2			September 2005
3			Sunday	Monday	Tuesday	Wednesday	Thursday	Fridy	Saturday
4			09-02-2005	09-03-2005	09-04-2005	09-05-2005	09-06-2005	09-07-2005	09-08-2005
5
Sheet3

How can we modified this formula:

=IF(G4="","",IF(MONTH(G4+1)<>MONTH(G4),"",TEXT(G4+1,"mm-dd-yyy")))

So I could put in C4 and copy accross to I4.

To get this result:

Calendar array.xls
	A	B	C	D	E	F	G	H	I
1	09-01-2005	Antonio Buenrostro
2			September 2005
3			Sunday	Monday	Tuesday	Wednesday	Thursday	Fridy	Saturday
4							09-01-2005	09-02-2005	09-03-2005
Sheet2

Its all depends for me to input the date in cell A1 and everything could be link.