alan.sluder
New member
- Joined
- Nov 11, 2014
- Messages
- 9
- Reaction score
- 0
- Points
- 1
- Excel Version(s)
- Excel 2013, 2016
I am using the vlookup in my workbook to lookup info in a table I have created and named using the name manager. Currently the formula I am using is as follows:
=IF(H11<>0,IF(H11<>"",VLOOKUP(H11,sstubalbr,VLOOKUP(O11,coln,2)),""),"")
Col H gives the size
Col O gives the item code/type (pipe, elbow, etc)
sstubalbr is the labor table I have defined
Both lookups work independtly.
I have added a new column L that i have called Labor Table. I would like to use this as the input for the labor table instead of hard coding like I have in example above. when I substitute the cell reference "L1" (ie) in place of the "sstubalbr" I get "#N/A" instead of the value I am trying to fetch from the labor table.
I have also defined a list/table called "Labor_Table_List" that i have tried using in place of the hard coded labor table name. So that the formula above now looks like this:
=IF(H11<>0,IF(H11<>"",VLOOKUP(H11,vlookup(L11,Labor_Table_List,1),VLOOKUP(O11,coln,2)),""),"")
This gives a "#Value" error.
Any suggestions? This may not be the best way to do it. I am trying to allow other users to input the labor table into col "L" so they don't have to edit the lookup formula
I have attached my spreadsheet. The tabs named "System *" are the ones with the formula in it in Col "s"
View attachment 2014 Mechanical Bid Form.xlsm
=IF(H11<>0,IF(H11<>"",VLOOKUP(H11,sstubalbr,VLOOKUP(O11,coln,2)),""),"")
Col H gives the size
Col O gives the item code/type (pipe, elbow, etc)
sstubalbr is the labor table I have defined
Both lookups work independtly.
I have added a new column L that i have called Labor Table. I would like to use this as the input for the labor table instead of hard coding like I have in example above. when I substitute the cell reference "L1" (ie) in place of the "sstubalbr" I get "#N/A" instead of the value I am trying to fetch from the labor table.
I have also defined a list/table called "Labor_Table_List" that i have tried using in place of the hard coded labor table name. So that the formula above now looks like this:
=IF(H11<>0,IF(H11<>"",VLOOKUP(H11,vlookup(L11,Labor_Table_List,1),VLOOKUP(O11,coln,2)),""),"")
This gives a "#Value" error.
Any suggestions? This may not be the best way to do it. I am trying to allow other users to input the labor table into col "L" so they don't have to edit the lookup formula
I have attached my spreadsheet. The tabs named "System *" are the ones with the formula in it in Col "s"
View attachment 2014 Mechanical Bid Form.xlsm