Horrendously Stuck (VLOOKUP)

%3CLINGO-SUB%20id%3D%22lingo-sub-394260%22%20slang%3D%22en-US%22%3EHorrendously%20Stuck%20(VLOOKUP)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394260%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20got%20a%20spreadsheet%20which%20I%20need%20hourly%20rates%20to%20be%20looked%20up%20and%20returned%20into%20a%20cell%20but%20read%20as%20%22%240.00%22%20if%20nothing%20has%20been%20entered%20in%20the%20Lookup%20Value%20cell.%20It%20was%20working%20fine%20yesterday%20with%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(LOOKUP(C6%2CRates!%24B%242%3A%24B%2410%2CRates!%24C%242%3A%24C%2410)%2C%22%240.00%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20returns%20a%20couple%20of%20the%20correct%20values%2C%20doesn't%20return%20anything%20at%20all%20or%20returns%20a%20random%20value%20from%20the%20Rates%20sheet.%20I'm%20beyond%20frustrated%20and%20cannot%2C%20for%20the%20life%20of%20me%2C%20figure%20out%20what's%20wrong.%20I've%20attached%20my%20file%20in%20hopes%20that%20someone%20can%20tell%20me%20what%20I'm%20doing%20wrong.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20achieve%3A%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EBe%20able%20to%20enter%20the%20%22Labour%20Code%22%20and%20have%20the%20corresponding%20hourly%20rate%20returned%20into%20the%20%22Rate%22%20cell%20via%20referencing%20a%20separate%20sheet.%20I%20know%20there's%20another%20way%20I%20could%20do%20this%20keeping%20it%20all%20on%20one%20sheet%20but%20at%20this%20point%2C%20it's%20determination%20and%20pride.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394260%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394282%22%20slang%3D%22en-US%22%3ERe%3A%20Horrendously%20Stuck%20(VLOOKUP)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394282%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313746%22%20target%3D%22_blank%22%3E%40morganmarcynuik%3C%2FA%3E%20%2C%20you%20need%20to%20sort%20you%20rates%20table%20by%20Code%20in%20ascending%20order%20to%20receive%20correct%20result.%20Better%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Rates!%24C%242%3A%24C%2410%2CMATCH(%24C6%2CRates!%24B%242%3A%24B%2410%2C0))%2C0)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I've got a spreadsheet which I need hourly rates to be looked up and returned into a cell but read as "$0.00" if nothing has been entered in the Lookup Value cell. It was working fine yesterday with the following formula:

 

=IFERROR(LOOKUP(C6,Rates!$B$2:$B$10,Rates!$C$2:$C$10),"$0.00")

 

Now it returns a couple of the correct values, doesn't return anything at all or returns a random value from the Rates sheet. I'm beyond frustrated and cannot, for the life of me, figure out what's wrong. I've attached my file in hopes that someone can tell me what I'm doing wrong. 

 

What I'm trying to achieve: 

Be able to enter the "Labour Code" and have the corresponding hourly rate returned into the "Rate" cell via referencing a separate sheet. I know there's another way I could do this keeping it all on one sheet but at this point, it's determination and pride. 

1 Reply
Highlighted

@morganmarcynuik , you need to sort you rates table by Code in ascending order to receive correct result. Better

=IFERROR(INDEX(Rates!$C$2:$C$10,MATCH($C6,Rates!$B$2:$B$10,0)),0)