Vlookup not working on Excel sheet
Hi guys
I'm completely new to VLOOKUP tables and have been working on some tutorials. I have an Excel sheet that I have attached so that you can see what I am trying to do.
So basically, in worksheet 'User Details' I would like the column 'Organisation' (column C) to be filled in with the data in worksheet 'Mailbox Details' called 'Organisation A (also column )'. If you look at cell C8 in 'User Details', you will see 'contoso.com' which I have manually put in there.
Here's how I am trying to do it:
In the 'User Details' worksheet, the column A6 is the value I am looking up. As an example, my Lookup_value will be 'A8'.
The Table_array will be worksheet 'Mailbox Details' columns 'C5:D29'.
Col_index_num = 1 as that's the column I am trying to extract data from.
Range_Lookup = false
The issue I am seeing though is that in 'Mailbox Details' worksheet, in Column C there is a formula that has been dragged down and perhaps this is causing the issue?
If you're able to help me figure this out, that would be grand.
Thanks for helping
Yashy
ActiveDevicesUsageTracker-07-06-201.xlsx
I'm completely new to VLOOKUP tables and have been working on some tutorials. I have an Excel sheet that I have attached so that you can see what I am trying to do.
So basically, in worksheet 'User Details' I would like the column 'Organisation' (column C) to be filled in with the data in worksheet 'Mailbox Details' called 'Organisation A (also column )'. If you look at cell C8 in 'User Details', you will see 'contoso.com' which I have manually put in there.
Here's how I am trying to do it:
In the 'User Details' worksheet, the column A6 is the value I am looking up. As an example, my Lookup_value will be 'A8'.
The Table_array will be worksheet 'Mailbox Details' columns 'C5:D29'.
Col_index_num = 1 as that's the column I am trying to extract data from.
Range_Lookup = false
The issue I am seeing though is that in 'Mailbox Details' worksheet, in Column C there is a formula that has been dragged down and perhaps this is causing the issue?
If you're able to help me figure this out, that would be grand.
Thanks for helping
Yashy
ActiveDevicesUsageTracker-07-06-201.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome Yashi!
The VLookup formula will only work if the loopup value is available in the first column of the lookup table i.e. it will search the lookup value in the first column of the lookup table which is not true in your case.
Index/Match formula doesn't have any such limitation. :)
The VLookup formula will only work if the loopup value is available in the first column of the lookup table i.e. it will search the lookup value in the first column of the lookup table which is not true in your case.
Index/Match formula doesn't have any such limitation. :)
ASKER
I see, I got it. Thanks for helping man. I definitely could benefit from tutorials!:
You're welcome Yashy! Glad you found it helpful.
ASKER
One thing, how do you know what the Table number is? Like in your formula you have Table 4?
Since that's a table so when you select the whole column, the structural table reference appears automatically. That's the advantage of using tables.
ASKER
But for example, in the same sheet, I can see that if I type the formula =IFERROR(Index(Table , the moment I type 'Table' it drops down with Table3 or Table 4. I don't know which one Table 3 is or Table 4? How do you know what table 3 is referring to and what table 4 is referring to?
If you click inside any Table, a Table Tools appears at the top middle of your ribbon and if you click the Design tab, on top left you will find the Table Name.
While using the table reference in a formula, if you just type "ta", all the available Tables (If table names start with the default Table word) in the workbook will be visible in the intellisense which appears as a drop down list and after picking the correct table, if you type an opening square bracket, you will find all the available columns in the selected table.
Refer to the following image.
While using the table reference in a formula, if you just type "ta", all the available Tables (If table names start with the default Table word) in the workbook will be visible in the intellisense which appears as a drop down list and after picking the correct table, if you type an opening square bracket, you will find all the available columns in the selected table.
Refer to the following image.
ASKER
Now that is an explanation and I love it man. Makes complete sense now:). Thank you sir.
Glad you found it helpful. :)
ASKER
Is there no way of this working with VLOOKUP in the same way?