Help with Excel formula - I'm a novice trying to learn

[25th QV]

Normally I can self serve with Youtube tutorials for simple stuff, but in this case, i don't know what category my query fits in to know what to search for.

I have a time sheet for my business and typically have to enter the person's name, the hours worked and the miles driven each time - the numbers are always the same miles and time, so I want to be able to ask excel to do something like this....

If I type Jones anywhere in col A, col B will automatically put in 3 hrs in col B and 12.7 miles in col C (numbers only is fine, I don't need the miles or hrs label, that is at the column header).

What I then need to do is have the same rule for multiple names and their respective hrs and miles down column A, so if I type Jones, Smith, Green, Davis etc, etc in Col A, it automatically fills in the known mileage driven and hrs worked in the requested cols as these are always the same - I guess referencing a tble that contains the customer name, the miles and hrs related to that specific customer?

I need a real Jack and Jill, start to finish, copy and paste, step by step instruction.... telling me to use 'Count IF" (or whatever the formula is) won't help - I'm really quite a novice and need exact instructions a 5 year old could follow. Do this, then type this here, then type that there, then drag that down to.... etc, etc.

Many thanks in advance.

[Mr Pointy]

Look at this XLOOKUP tutorial:

https://www.youtube.com/watch?v=xnLvEhXWSas

I know you say you are a beginner but this should put you on the right track & her examples are pretty close to what you want to do.

[25th QV]

Thank you.

[Mr Pointy]

25th QV said:

Thank you.

If you get stuck then post again & maybe someone can give you a steer.

[Countdown]

25th QV said:

I guess referencing a tble that contains the customer name, the miles and hrs related to that specific customer?

Yes, that's it, in a nutshell.

Step 1
Set up a table which contains the permanent values for each person;
Column A should be the name
Column B could be the hours worked
Column C could be the miles driven

Step 2
In your timesheet let's assume that Column A is where you type in the name.
In columns B and C you would use VLOOKUP formula which looks at the value in Column A, compares it to the Table you set up above, and returns the values in Column B and Column C

That probably doesnt make sense. If you want to email me a copy of the timesheet it would only take a few minutes for me to set it up for you

[Mr Pointy]

Countdown said:

Step 2
In your timesheet let's assume that Column A is where you type in the name.
In columns B and C you would use VLOOKUP formula which looks at the value in Column A, compares it to the Table you set up above, and returns the values in Column B and Column C

Any particular reason why you wouldn't use XLOOKUP?

[C n C]

Mr Pointy said:

Countdown said:

Step 2
In your timesheet let's assume that Column A is where you type in the name.
In columns B and C you would use VLOOKUP formula which looks at the value in Column A, compares it to the Table you set up above, and returns the values in Column B and Column C

Any particular reason why you wouldn't use XLOOKUP?

Comparison between XLOOKUP and VLOOKUP here.

OP can use either if on Office365, but if running an older copy of Excel, would have to use VLOOKUP as I understand XLOOKUP was only made available in Office365?

[Countdown]

Mr Pointy said:

Any particular reason why you wouldn't use XLOOKUP?

Because I'd never heard of it before today

[paulrockliffe]

First thing - format your data input table as a table it'll make things easier and if you're learning, you should learn proper data structuring. Foprmat your lookup tables as tables too.

Don't forget Data Validation:

Create a lookup table of people.
Format your input table as a table.
Select the name column and apply data validation.
Select 'from a list' point the list at your lookup table and choose the from drop-down option.

Then when you add a row you can select the person from drop-down, or autofill from typing. You avoid the issue of breakign your formulas by not putting a valid name in.

Formatting as tables means if you add a new person to the person lookup they'll be an option in your drop-down without having to redefine the validation range. IN your input table formauls wil automatically fill-down too.