Vlookup indirect

[Alex O]

I'm trying to get my formula in Y3 to search another sheet (Week1) and within the range u2:v14500 find the value in x3 and return the value 1 column over. The formula I'm trying to use is below and failing muiserably. Can anyone help me figure out what is wrong here....

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 73px"><COL style="WIDTH: 47px"><COL style="WIDTH: 27px"><COL style="WIDTH: 73px"><COL style="WIDTH: 59px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>U</TD><TD>V</TD><TD>W</TD><TD>X</TD><TD>Y</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD> </TD><TD>Week1</TD><TD> </TD></TR><TR style="HEIGHT: 33px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold">This Week</TD><TD style="FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold"> </TD><TD style="FONT-FAMILY: Arial; COLOR: #ff0000; FONT-SIZE: 10pt; FONT-WEIGHT: bold">Last Week</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">297788</TD><TD style="TEXT-ALIGN: center">1</TD><TD> </TD><TD style="TEXT-ALIGN: right">297788</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">274840</TD><TD style="TEXT-ALIGN: center">2</TD><TD> </TD><TD style="TEXT-ALIGN: right">274840</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>V3</TD><TD>=IF(C3="","",IF(ISNA(VLOOKUP($U3,$C$3:$H$7021,6,FALSE)),"",VLOOKUP($U3,$C$3:$H$7021,6,FALSE)))</TD></TR><TR><TD>X3</TD><TD>=IF(INDIRECT($X$1&"!U"&ROW())>0,INDIRECT($X$1&"!U"&ROW()),"")</TD></TR><TR><TD>Y3</TD><TD>=IF(COUNTIF(INDIRECT(X1&"!"&"$u$2:$v$14500"),X1), VLOOKUP(X3,INDIRECT(X1&"!"&"$u$2:$v$14500"),2,0),"")</TD></TR><TR><TD>V4</TD><TD>=IF(C4="","",IF(ISNA(VLOOKUP($U4,$C$3:$H$7021,6,FALSE)),"",VLOOKUP($U4,$C$3:$H$7021,6,FALSE)))</TD></TR><TR><TD>X4</TD><TD>=IF(INDIRECT($X$1&"!U"&ROW())>0,INDIRECT($X$1&"!U"&ROW()),"")</TD></TR><TR><TD>Y4</TD><TD>=IF(AI5="","",VLOOKUP(X4,$AI$4:$AJ$5938,2,FALSE))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[Akashwani]

Hi Alex,

I'm not an expert and this may not be the best solution but it appears to work...

Excel Workbook
	U	V
1	ID Number	Name
2	AA10000	Bart Simpson
3	AA10001	Lisa Simpson
4	AA10002	Grandpa Simpson
5	AA10003	Lisa Simpson
6	AA10004	Marge Simpson
7	AA10005	Maggie Simpson
8	AA10006	Milhouse Van Houten
9	AA10007	Ned Flanders
10	AA10008	Otto Mann
11	AA10009	Rod Flanders
12	AA10010	Tod Flanders
13	AA10011	Bart Simpson
14	AA10012	Moe Szyslak
15	AA10013	Grandpa Simpson
16	AA10014	Lisa Simpson
17	AA10015	Marge Simpson
18	AA10016	Maggie Simpson
19	AA10017	Bart Simpson
20	AA10018	Lisa Simpson
Week1

Excel Workbook
	X	Y
1	Week1	*
2	ID Number	Name
3	AA10002	Grandpa Simpson
4	AA10011	Bart Simpson
Result

I hope that helps give you some idea if not a solution.

Ak

 

[MrKowz]

Akashwani has the correct formula, however it does not need to be array entered. Just use:

=INDEX(INDIRECT("'"&$X$1&"'!$V$2:$V$14500"),MATCH($X3,INDIRECT("'"&$X$1&"'!$U$2:$U$14500"),0))

 

[jasonb75]

Your formula in Y3 is wrong

=IF(COUNTIF(INDIRECT(X1&"!"&"$u$2:$v$14500"),X1), VLOOKUP(X3,INDIRECT(X1&"!"&"$u$2:$v$14500"),2,0),"")

It look like X1 Should be X3

 

[Akashwani]

Hi Keith,

I didn't realise I'd done ctrl shift enter

How's the new job?
I hope everything is going well for you.

Ak

 

[Alex O]

Thanks for all of the input! Much appreciated, Akashwani. Your solution worked perfectly!

Cheers

 

[MrKowz]

Hi Keith,

I didn't realise I'd done ctrl shift enter

How's the new job?
I hope everything is going well for you.

Ak

Things are fantastic. Loving it; though it definitely doesn't give me enough time to help on the forums during the day.