IF and LOOKLEFT and LOOKRIGHT functions

[bill351]

Hey Guys n Girls I am trying to automate a data spreadsheet with the above functions.

What I have is spreadsheet to record Higher Duties and Column A is Payroll Number Column B is the staff name via a drop down box and Column C is their current pay increment so I have started on a formula =IF(AND(B3=Data!B2:B128),LOOKLEFT,Data!A2:A128) that is in Higher Duties A3 and what I am trying to achieve is a pick from the drop down box a staff name "Bob" in Data B3 it then looks in the DATA sheet B2:B128 finds "Bob" and looks left and enters his payroll number in Higher Duties A3

Also same as above "Bob" is in B3 and =IF(AND(B3=Data!B2:B128),LOOKLRIGHT,Data!C2:C128) and the formula would look through C2:C128 find Bob,s increment and that would appear in Higher Duties C3.

looking at this it looks messy and I hope I have explained well enough

Many thanks in advance

Cheers from Oz

 

[arthurbr]

Never heard of LOOKLEFT and LOOKRIGHT functions. Which XL version are they from? Google does not return anything

 

[jtakw]

Never heard of LOOKLEFT and LOOKRIGHT functions. Which XL version are they from? Google does not return anything

OP must be a Beta Tester for Excel 2030

 

[vmjan02]

OP must be a Beta Tester for Excel 2030

It has to be left and right function.

 

[vmjan02]

I feel you just need a simple vlookup.

enter your employee name in cell E1 and apply formula =VLOOKUP(E1,B2:C6,2,0)
here column B will have all the employee names and column c will have increment values.

 

[Fluff]

It has to be left and right function.

Maybe custom functions.
@bill351
If these are custom functions,can you please supply the code

 

[RoryA]

They are either defined names (as there are no brackets afterwards, so can't be functions), or they are just placeholders for the function the OP wants created, I guess.

 

[arthurbr]

They are either defined names (as there are no brackets afterwards, so can't be functions), or they are just placeholders for the function the OP wants created, I guess.

Seems logical

 

[bill351]

Thank you vmjan02 that looks like it will work and please excuse the clumsiness of how I tried to explain what I needed to do

 

[vmjan02]

Thank you vmjan02 that looks like it will work and please excuse the clumsiness of how I tried to explain what I needed to do

Welcome