Vlookup Table Array
- Thread starter chriswallace85
- Start date
chriswallace85
Striker
Divvy.
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)
Last edited:
Heffer
Winger
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.
Last edited:
chriswallace85
Striker
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.
Last edited:
Heffer
Winger
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'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.
Do you have a named range defined anywhere? Or has it setup a named range as part of the array creation?
chriswallace85
Striker
I had a lot of that here!
It was driving me nuts. I think the basic problem came from the cobbled together workbook (not mine) that I was sourcing the data from. Once I'd pulled it all out and cleaned it up (something I was hoping to avoid) it's running. Awkward bloody thing.
I did have, but I was wanting to create two new ones. We're way past that now though thank fuck.
Last edited:
Gazoliver
Striker
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.
FootballFan
Striker
Hope this gets sorted soon
Darlo1973
Striker
INDEX and MATCH is much more flexible that VLOOKUP.
www.excelcampus.com
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
chriswallace85
Striker
Sounds like a dodgy 'search' category that mind.
Heffer
Winger
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.
Gazoliver
Striker
Yeah the desktop version is very very good.
THELORDRODWELL
Striker
Ask someone who knows about spreadsheets
Sounds like a dodgy 'search' category that mind.
Power BI Hookup
Shit I meant Lookup…
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.
I built a spreadsheet that would fill in Dog Pedigrees and Dog Show Entries using Index and Match…
Last edited:
Thurston
Winger
Xlookup is where it’s at.
(don’t ask why I’m looking at 2 year old threads about Excel.)
