Lookup in data table - too many arguments?
My IT department is currently working on a way to convert my business from
Excel to a MS SQL database, but until then I need figure out a way to maintain my daily functionality and meet the demands of my clients. I'm trying to create a formula that will automatically insert a job costing code which will be referenced on another worksheet to fill in Costs and Accruals. Here is the relevane layout of my spreadsheet: Worksheet A: A- Origin/Shipper B- Destination W- Job Cost Code Worksheet C: B- Destination Name C- Store Number/Job Cost Code What I would like to do, is have Excel lookup the name of the desination and display the appropriate job cost code as outlined on the table in Worksheet C. I came up with the following formula: =IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C)) The problem is, that not all of the destinations appear on the data table in Worksheet C. If that happens, I want Excel to lookup the value in column A, the origin. But it returns the value for the closest match instead of returning a FALSE value. I thought that this would work: =IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C)) That just returns a circular argument, and no matter what setting I put in 'iteration' it comes back as a 0. I thought that by taking the circular argument out of the formula it might work, so added an outside reference in Column Y: =IF(B=WorksheetC!B,TRUE(),FALSE()) Then changed W to: =IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C)) All I get now is a #VALUE! result in W. Any suggestions? -- Stephen Nichols Freight Management Services Cole International |
Lookup in data table - too many arguments?
Some thoughts ..
In sheet: A, (data is assumed from row2 down) Try in W2: =IF(B2="","",IF(ISNA(VLOOKUP(B2,'C'!B:C,2,0)),IF(I SNA(VLOOKUP(A2,'C'!B:C,2,0)),"--",VLOOKUP(A2,'C'!B:C,2,0)),VLOOKUP(B2,'C'!B:C,2,0) )) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Canadian Chai Guy" wrote: My IT department is currently working on a way to convert my business from Excel to a MS SQL database, but until then I need figure out a way to maintain my daily functionality and meet the demands of my clients. I'm trying to create a formula that will automatically insert a job costing code which will be referenced on another worksheet to fill in Costs and Accruals. Here is the relevane layout of my spreadsheet: Worksheet A: A- Origin/Shipper B- Destination W- Job Cost Code Worksheet C: B- Destination Name C- Store Number/Job Cost Code What I would like to do, is have Excel lookup the name of the desination and display the appropriate job cost code as outlined on the table in Worksheet C. I came up with the following formula: =IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C)) The problem is, that not all of the destinations appear on the data table in Worksheet C. If that happens, I want Excel to lookup the value in column A, the origin. But it returns the value for the closest match instead of returning a FALSE value. I thought that this would work: =IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C)) That just returns a circular argument, and no matter what setting I put in 'iteration' it comes back as a 0. I thought that by taking the circular argument out of the formula it might work, so added an outside reference in Column Y: =IF(B=WorksheetC!B,TRUE(),FALSE()) Then changed W to: =IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C)) All I get now is a #VALUE! result in W. Any suggestions? -- Stephen Nichols Freight Management Services Cole International |
Lookup in data table - too many arguments?
It worked.
You're amazing! -- Stephen Nichols Freight Management Services Cole International "Max" wrote: Some thoughts .. In sheet: A, (data is assumed from row2 down) Try in W2: =IF(B2="","",IF(ISNA(VLOOKUP(B2,'C'!B:C,2,0)),IF(I SNA(VLOOKUP(A2,'C'!B:C,2,0)),"--",VLOOKUP(A2,'C'!B:C,2,0)),VLOOKUP(B2,'C'!B:C,2,0) )) Copy down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Canadian Chai Guy" wrote: My IT department is currently working on a way to convert my business from Excel to a MS SQL database, but until then I need figure out a way to maintain my daily functionality and meet the demands of my clients. I'm trying to create a formula that will automatically insert a job costing code which will be referenced on another worksheet to fill in Costs and Accruals. Here is the relevane layout of my spreadsheet: Worksheet A: A- Origin/Shipper B- Destination W- Job Cost Code Worksheet C: B- Destination Name C- Store Number/Job Cost Code What I would like to do, is have Excel lookup the name of the desination and display the appropriate job cost code as outlined on the table in Worksheet C. I came up with the following formula: =IF(B=WorksheetC!B,LOOKUP(B,WorksheeC!B,WorksheetC !C)) The problem is, that not all of the destinations appear on the data table in Worksheet C. If that happens, I want Excel to lookup the value in column A, the origin. But it returns the value for the closest match instead of returning a FALSE value. I thought that this would work: =IF(B=WorksheetC!B,LOOKUP(B,WorksheetC!B,Worksheet C!C))*AND(IF(W=FALSE(),(LOOKUP(A,WorksheetC!B,Work sheetC!C)) That just returns a circular argument, and no matter what setting I put in 'iteration' it comes back as a 0. I thought that by taking the circular argument out of the formula it might work, so added an outside reference in Column Y: =IF(B=WorksheetC!B,TRUE(),FALSE()) Then changed W to: =IF(Y=TRUE(),LOOKUP(B,WorksheetC!B,WorksheetC!C))* AND(IF(Y=FALSE(),LOOKUP(A,WorksheetC!B,WorksheetC! C)) All I get now is a #VALUE! result in W. Any suggestions? -- Stephen Nichols Freight Management Services Cole International |
Lookup in data table - too many arguments?
Great to hear that, Stephen.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Canadian Chai Guy" wrote in message ... It worked. You're amazing! -- Stephen Nichols |
All times are GMT +1. The time now is 01:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com