How do I lookup a particular cell in another worksheet dependent on the value of a cell?
I am trying to read in a table called operations that looks like so
"id";"name";
"1";"LASER CUTTING";
"2";"DEBURR";
"3";"MACHINING";
"4";"BENDING";
"5";"PEM";
"6";"WELDING";
"7";"PAINT PREPARATION";
"8";"PAINTING";
"9";"SILKSCREEN PREPARATION";
"10";"SILKSCREEN";
"11";"ASSEMBLY - PACKAGING";
"12";"LASER PREP";
I want to have a column in a worksheet that gets the appropriate name based on value of an operation_id column in another worksheet. How do I lookup a 开发者_JAVA技巧particular cell in another worksheet dependent on the value of a cell?
Example
userid, operation_id, operation_name
bob, 3, MACHINING
You should look at the DGET(database,field,criteria) function, reference here.
Or you can use this worksheet function:
VLOOKUP(cellWithID, Sheet2!A1:B13, 2, FALSE)
where cellWithID is the cell with the ID value you want to use.
Maybe the Lookup() function would work better for you. http://www.techonthenet.com/excel/formulas/lookup.php
Basing off this: "What I really want to do is just lookup the name for an operation without having to run a sql query every time or have an ugly huge if statement in every cell."
I guess what confuses me here is why you don't just use a join. You can always join that table as a lookup to whatever your sql statement is;
select operations.name, tableA.* from tableA
left outer join operations on operations.id = tableA.operationid
If you wanted to, you could functionize this; not recommended. Subqueries are generally speaking bad news. However,
create function dbo.LookupOperationName
(
@id int
)
returns varchar(100)
as
declare @returnvalue varchar(100)
select @returnvalue = name from Operations where id = @id
return @returnValue
would do the trick. Then you could:
select tablea.*,LookupOperationName(operationid) from tablea
Again, remember that your join example is much more performant. You could also create a view that had the join, and use the view in place of the table.... all kinds of things.
精彩评论