Lookup in a different worksheet based on an entered field
Posted by Amy on September 28, 2001 6:53 AM
I have 12 worksheets labeled January - December.
On another worksheet I want the user to enter a month in cell F4 (through a list I created with data validation) and have the formula lookup a value in the corresponding worksheet.
Once the month worksheet is determined, i was going to use the VLookup formula to find "Department" in the first column of the table labeled "Name" and return the corresponding value in the column W labeled "TSF"
I cannot get the VLOOKUP function to work even when I specify the month worksheet to go to.
Posted by Aladin Akyurek on September 28, 2001 7:24 AM
Amy,
=VLOOKUP(lookup-values,INDIRECT(F4&"!"&"$A$2:$B$200"),2,0)
will do the job.
I assume (actually suppose) that all of your montly sheets are structured in the same way. That is, department and ISF values are all in the same columns across all sheets. Insert that range in the above formula.
If you don't want to see #N/A values, use
=IF(COUNTIF(INDIRECT(F4&"!"&"$A$2:$B$200"),lookup-value), VLOOKUP(lookup-values,INDIRECT(F4&"!"&"$A$2:$B$200"),2,0),"")
Aladin
Posted by Amy on September 28, 2001 8:08 AM
It did not work. I entered the range which is A2:A)145. And I entered the lookup value- "Department" from the first column. The TSF values are always in column #23 or column W, so I entered 23 as the column_num argument.
This is what I came up with:
=IF(COUNTIF(INDIRECT(F4&"!"&"$A$2:$AO$145"),Department), VLOOKUP(Department,INDIRECT(F4&"!"&"$A$2:$AO$145"),23,0),"")
It returns a blank box at this time. I am sure I am missing something fundamental.
Posted by Aladin Akyurek on September 28, 2001 8:17 AM
Department must be a value like or refer to a value.
Put the department you're interested in, say, Accounting, directly in the formula between double quotes, that is,
=IF(COUNTIF(INDIRECT(F4&"!"&"$A$2:$AO$145"),"Accounting"), VLOOKUP("Accounting",INDIRECT(F4&"!"&"$A$2:$AO$145"),23,0),"")
Or enter the department of interest in some cell, say, in G4 and use this in the formula:
=IF(COUNTIF(INDIRECT(F4&"!"&"$A$2:$AO$145"),G4), VLOOKUP(G4,INDIRECT(F4&"!"&"$A$2:$AO$145"),23,0),"")
Aladin
