Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Divvy.It's nice that in this day and age it's a lot more acceptable to have men secretaries aswell. Fair play to the op.
Xlookup (like Vlookip) is working off one of the new data sheets and not the other - the must be something wrong with sheet itself and I cannot fathom it.You can look forward to using XLOOKUP in Office 365 in the future
(just double checked my internet history and it wasnt what I thought it was)
If it returns N/A it means there is no corresponding value generated by the formula, so it is likely you are looking up a blank cell. Do you have the value you are looking up in the first column of the array? How many columns have you specified? Again, not easy to troubleshoot when you can't see exactly how you have it laid out but it almost sounds like you are setting the formula to count too few/many columns along if it is actually returning a value.In the workbook where the vlookup works, the formula returns zeros and pound signs when the lookup cell is blank, but in my new workbook, the same formula is returning #n/a. It's driving is nuts. Any ideas?
Google wants me to go down the IF route, but there's no IF formula in my original working workbook. It's just Vlookup.
The data has always been on a separate sheet.If it returns N/A it means there is no corresponding value generated by the formula, so it is likely you are looking up a blank cell. Do you have the value you are looking up in the first column of the array? How many columns have you specified? Again, not easy to troubleshoot when you can't see exactly how you have it laid out but it almost sounds like you are setting the formula to count too few/many columns along if it is actually returning a value.
Another thing to try is Evaluate Formula. Click in the cell and go to Formulas then Evaluate Formula and it will go through each stage of the formula argument by argument. This might help you see where it is going wrong.
Also, no need to have the data you are looking up in the same sheet. That's why I suggested naming the range as it doesn't matter if the data and formula are on different sheets.
If you are struggling to understand how to do this look here: Excel Named range | Exceljet
Xlookup allows you to lookup both ways whereas VLookup is restricted to the left. You must have the lookup value in the first column otherwise excel won't know where to start before counting along columns based on the 3rd argument in the formula.
You make a good point there sometimes having different formats can cause problems. I often work with data which has leading zeroes in the ID number and unless you format the column as text or add a leading apostrophe to display the number as text it can cause a mismatch.The data has always been on a separate sheet.
The original working sheet has the table with the data in it on sheet 1 (named), and the second sheet with the Vlookup formulas looking up a code in the leftmost column, and returning various info from that row. It's fine, it works. When my lookup cell is blank the formulas return either a 0 or a £ sign.
What I want to do is create a similar workbook to do the same job looking at two different versions of the data. So sheet 1 - schedule 1. Sheet 2 - schedule 2. Sheet 3 - my lookup sheet, with forumlas in looking at either sheet 1 or sheet 2. I'm getting half way there with the Xlookup - but once I started trying to look at two sheets (not in the same formula mind you) it was throwing a wobbler.
I've ended up having to amalgamate the two versions of the data into one sheet (getting rid of the relevant duplicates) and set up an xlookup to return what I want. I think it's just about working. It's driving me nuts as to why the original Vlookup wouldn't work though, and why I can't look at multiple sheets.
I've done an error check, and despite me trying to format the cells (fuckers!) to numbers previously, it obviously hasn't made the change without me going in and refreshing each one. I think that could be the root of all my problems.
I think there's been an inconsistency in the data format I was searching. Simply formatting wasn't refreshing it properly. I've had to use a text to columns hack to properly format thousands of cells to bring them in line. I think it's working. What a clart on.
I had a lot of that here!You make a good point there sometimes having different formats can cause problems. I often work with data which has leading zeroes in the ID number and unless you format the column as text or add a leading apostrophe to display the number as text it can cause a mismatch.
I'm glad you got sorted though! Like I say it's hard to help with these sort of things when you can't see the sheet.
I did have, but I was wanting to create two new ones. We're way past that now though thank fuck.Do you have a named range defined anywhere? Or has it setup a named range as part of the array creation?
Have a look at power bi if you haven't already...The data has always been on a separate sheet.
The original working sheet has the table with the data in it on sheet 1 (named), and the second sheet with the Vlookup formulas looking up a code in the leftmost column, and returning various info from that row. It's fine, it works. When my lookup cell is blank the formulas return either a 0 or a £ sign.
What I want to do is create a similar workbook to do the same job looking at two different versions of the data. So sheet 1 - schedule 1. Sheet 2 - schedule 2. Sheet 3 - my lookup sheet, with forumlas in looking at either sheet 1 or sheet 2. I'm getting half way there with the Xlookup - but once I started trying to look at two sheets (not in the same formula mind you) it was throwing a wobbler.
I've ended up having to amalgamate the two versions of the data into one sheet (getting rid of the relevant duplicates) and set up an xlookup to return what I want. I think it's just about working. It's driving me nuts as to why the original Vlookup wouldn't work though, and why I can't look at multiple sheets.
I've done an error check, and despite me trying to format the cells (fuckers!) to numbers previously, it obviously hasn't made the change without me going in and refreshing each one. I think that could be the root of all my problems.
I think there's been an inconsistency in the data format I was searching. Simply formatting wasn't refreshing it properly. I've had to use a text to columns hack to properly format thousands of cells to bring them in line. I think it's working. What a clart on.
Sounds like a dodgy 'search' category that mind.Have a look at power bi if you haven't already...
I'm currently learning it at work. Used Tableau in the past but we are bringing Power BI in imminently and as the data analyst it's going to be my responsibility. From what I have seen so far it's a good bit of kit.Have a look at power bi if you haven't already...
Yeah the desktop version is very very good.I'm currently learning it at work. Used Tableau in the past but we are bringing Power BI in imminently and as the data analyst it's going to be my responsibility. From what I have seen so far it's a good bit of kit.
Sounds like a dodgy 'search' category that mind.
INDEX and MATCH is much more flexible that VLOOKUP.
How to Use Index Match Instead of Vlookup - Excel Campus
Learn the advantages of using INDEX MATCH instead of VLOOKUP. Incl practice Excel file, video tutorial on how to write the formulas, and preventing errors.www.excelcampus.com
Xlookup is where it’s at.Vlookup!
*Shudder*
Copy and paste the data which you are using as your table array into the same workbook as VLOOKUP as this will be a be less of a risk
Make sure your lookup value is in the correct column too.