Vlookup Help!!!

[checkoutgirl]

Can't seem to get my head around it at all. I have a source sheet with a full Vehicle Reg Column and an empty Driver Name column beside it. I need to grab the driver names from another sheet on the same workbook and put them in the empty Driver Name column.

I need to sort this out but feel like I've been asked to run the hadron collider!

Can anyone help please?

[Mr_Simnock]

https://www.perfectxl.com/excel-glossary/how-to-use-vlookup-excel/

[Shaxberd]

If you're running Office 365 or a version more recent than Excel 2019, use XLOOKUP instead, it's a lot more intuitive. I've never been able to quite get VLOOKUP to do what I want it to but with XLOOKUP you can just point it at the column you want to look data up in without the extra fiddling around.

[Mr_Simnock]

XLOOKUP requires more details to be put in than VLOOKUP, how is it more intuative.

Edit: Looking into XLOOKUP you can get away with just three bits of info to get it to work instead of 4.

[Good Hank]

Don't bother with VLOOKUP, it's pants. Use INDEX MATCH:

=INDEX(Driver Name column range,MATCH(unique search criteria,Vehicle Reg column range,0))

Although it sounds like you don't have a search criteria at the moment. Basically INDEX MATCH is a column search crossed with a row search, in that the first bit is returned by the row/column search of the last two bits.

[Good Hank]

XLOOKUP is good too.

[Mr_Simnock]

VLOOKUP is fine, all these suggestions just do roughly the same thing in slightly different ways, neither is 'better' than the other. Never use Excel much for stuff like this, allways prefer my tables to be right before bringing into excel (which is rare now) using SQL.

[ZoyzaSorris]

This is the kind of stuff chat-gpt is actually quite good at

[ZoyzaSorris]

(Whereas I correctly surmised early on that it is a load of overhyped nonsense and not AI early on)

[touchingcloth]

XLOOKUP requires more details to be put in than VLOOKUP, how is it more intuative.

Edit: Looking into XLOOKUP you can get away with just three bits of info to get it to work instead of 4.

It's more intuitive than VLOOKUP - you give it your lookup range and your return range as separate inputs, whereas VLOOKUP wants you to give a range that includes both the lookup AND return columns, with you telling the expression how many columns to the right (or a negative number if you're going to the left) of the lookup your desired return values are.

[Mr_Simnock]

I dont find either more intuative than the other, you do know that differs from person to person, it's all easy peasy stuff anyway

[checkoutgirl]

Thanks lads. I think I cracked it but only for the first cell. Replicating it down is a problem but it's something to build on!

I thought it was going to ruin my day off.

[touchingcloth]

True enough. @checkoutgirl HARK, MORON

[touchingcloth]

Thanks lads. I think I cracked it but only for the first cell. Replicating it down is a problem but it's something to build on!

I thought it was going to ruin my day off.

Click in that cell and hit ctrl+d?

[checkoutgirl]

Don't bother with VLOOKUP, it's pants. Use INDEX MATCH:

=INDEX(Driver Name column range,MATCH(unique search criteria,Vehicle Reg column range,0))

Although it sounds like you don't have a search criteria at the moment. Basically INDEX MATCH is a column search crossed with a row search, in that the first bit is returned by the row/column search of the last two bits.

Thanks I'll give this a go. That's enough internet hacking for one day, where's my Tab?

[Sean Ymphs]

Be sure to put the dollar signs in your lookup index ranges, like this $A$1:$D$100 - so they stay the same when you drag

[checkoutgirl]

True enough. @checkoutgirl HARK, MORON

Some folk are just left brained. Doing remedial maths in school and coming out in a cold sweat over algebra.

[Mr_Simnock]

The only time I ever struggled a bit with Excel is trying to work out how to get a slicer result to change how a time base DAX calc worked.

EDIT: Using MDX can be a bit of a bugger too at times

[checkoutgirl]

It's more intuitive than VLOOKUP - you give it your lookup range and your return range as separate inputs, whereas VLOOKUP wants you to give a range that includes both the lookup AND return columns, with you telling the expression how many columns to the right (or a negative number if you're going to the left) of the lookup your desired return values are.

This is all very complicated stuff!

[Mr_Simnock]

This is all very complicated stuff!

It's surprising how so many people can't use 99.9% of Excel functionality

[Blumf]

This all sounds like it should be done in a database


https://archive.org/details/mangaguidetodata0000taka

[chuckles]

wow mr simnock you're really good

[Cuellar]

It's surprising how so many people can't use 99.9% of Excel functionality

I look on those who can't use 99.9% of excel's functionality as being in a state of prelapsarian bliss.

[Zero Gravitas]

I used to work with a business intelligence team, with a admin called Vee on it, and their manager thought that all references to a "Vlookup" were some kind of in-joke regarding asking her to assist.

Stupid fucking cunt.

[Sebastian Cobb]

I look on those who can't use 99.9% of excel's functionality as being in a state of prelapsarian bliss.

Yeah, safe to say if using it no longer makes you feel like you're the guy in Wake in Fright you need to do some serious introspection.

[Cuellar]

>mfw I do a vlookup

[touchingcloth]

Yeah, safe to say if using it no longer makes you feel like you're the guy in Wake in Fright you need to do some serious introspection.

The only people I know who use the lesser known features of Excel are finance types, and fair fucks, when I see what sort of data their spreadsheets contain I bet coding up a bit of VBA feels like sweet relief from the thoughts of their eventual death that must occupy their minds the rest of the time.

[touchingcloth]

I used to work with a business intelligence team, with a admin called Vee on it, and their manager thought that all references to a "Vlookup" were some kind of in-joke regarding asking her to assist.

Stupid fucking cunt.

Our business intelligence team have exhausted every possible pun on BI that exists, so hiring someone called Vee into the team sounds like it would be a lovely change. Are they looking for work?

[Dex Sawash]

Just burn the cars, sorted

[Paul Calf]

ChatGPT:

Certainly, to pull the driver names from another sheet in the same workbook and place them beside the respective Vehicle Reg in the main sheet, you can use the `VLOOKUP` function. Here's a step-by-step guide to achieve this:

1. **Prepare Your Sheets**:
    - Assume Sheet1 contains the Vehicle Reg and the empty Driver Name columns.
    - Assume Sheet2 contains the list of Vehicle Reg and their corresponding driver names.

2. **Using VLOOKUP**:
    - Go to Sheet1.
    - Click on the first cell of the empty Driver Name column.
    - Enter the following formula:
    ```excel
    =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"")
    ```
    Note: This assumes the Vehicle Reg is in column A of both sheets and the Driver Name is in column B of Sheet2.

3. **Drag Down the Formula**:
    - After entering the formula, move your mouse cursor to the bottom right corner of the cell (you should see a small square or "handle").
    - Click and drag this handle down to copy the formula for all the rows in your list.

4. **Explanation**:
    - `VLOOKUP` checks the Vehicle Reg in Sheet1 (Column A) and searches for it in Sheet2 (Column A).
    - If it finds a match, it pulls the driver name from the second column (Column B) of Sheet2 and places it in the empty Driver Name column of Sheet1.
    - `IFERROR` is used to handle any errors (e.g., if a Vehicle Reg is not found in Sheet2), and instead of displaying an error, it will show an empty cell.

This approach will fill the empty Driver Name column in Sheet1 with driver names from Sheet2 corresponding to the Vehicle Reg. If a Vehicle Reg is not found in Sheet2, the Driver Name will remain blank in Sheet1.

Both `VLOOKUP` and `XLOOKUP` are powerful lookup functions in Excel, but they have different features and use cases. Here's a comparison:

1. **Availability**:
  - `VLOOKUP`: Available in older and newer versions of Excel.
  - `XLOOKUP`: Introduced in Excel for Microsoft 365 and Excel 2019, so it's not available in older versions.

2. **Search Direction**:
  - `VLOOKUP`: Searches vertically in the first column of a table.
  - `XLOOKUP`: Can search both vertically and horizontally, providing more flexibility than VLOOKUP.

3. **Column Reference**:
  - `VLOOKUP`: Requires a column index number to return the value from. This can be problematic if columns are added or removed.
  - `XLOOKUP`: Uses the actual target return column/range, so it's less affected by changes in table structure.

4. **Error Handling**:
  - `VLOOKUP`: Requires additional functions like `IFERROR` to handle errors gracefully.
  - `XLOOKUP`: Has built-in error handling with optional arguments for not found values.

5. **Search Mode**:
  - `VLOOKUP`: By default, it does an approximate match, which can lead to unexpected results. You need to specify `FALSE` for an exact match.
  - `XLOOKUP`: Defaults to exact match, reducing chances of unexpected results.

6. **Lookup Array Flexibility**:
  - `VLOOKUP`: Requires the lookup column to be the first in the range.
  - `XLOOKUP`: Can look up a value from any column, not just the first.

7. **Return Multiple Columns or Rows**:
  - `VLOOKUP`: Can only return one column per function call.
  - `XLOOKUP`: Can return multiple columns or rows with a single function call.

8. **Wildcard Characters**:
  - `VLOOKUP`: Supports wildcards like `?` and `*` in the lookup value.
  - `XLOOKUP`: Doesn't support wildcards in the standard function call but can with some workarounds.

In conclusion, if you're using a version of Excel that supports `XLOOKUP`, it is generally more versatile and user-friendly than `VLOOKUP`, especially for complex tasks. However, if you're working with older versions or sharing files with users who might have older versions, `VLOOKUP` might be more universally applicable.