Hello..
I have acquired a #VALUE error which is showing up as a data type error. But I don't understand. I tried simplifying the table and making all data types the same... all numbers, and still it throws an error. I recognize that it could be a column length issue, but in the case of my example with no missing data, the error still exists.
Here is a pic of my example. See formula in formula bar.
What am I doing wrong?
Gary
clipped example.png
Lookup requires the lookup value, the lookup array, the return array, [Match mode], [Search mode], with the last two optional. So your function as indicated cannot work. Then there's the logic of your function.... very questionable. Why are you trying to find cell M2 value if it needs to match cell N2?
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
It's difficult to know what results you do want when presented with something that doesn't do what you want.
So what results are you expecting?
A guess, in cell P2:
=XLOOKUP(M2,$M$2:$M$5,$N$2:$N$5,"")
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
Just a guess as to what I think you're looking for, but the formulas in col P are all the same
Since they're all lookin to XMATCH K2, they all return the same value
Capture.JPG
Otherwise it might be somnething like this
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Originally Posted by p45cal
Yes, I thought I was making it clear, but when you don't fully comprehend the function, then it's not always easy to know how to ask the question. Anyway, you all helped clear it up. Thanks.
To clarify... I didn't present the actual need because I would have had to show you two spreadsheets and the formula I had and how it wasn't working. I was tired from several hours of trying to figure it out and wanted to get the question out there. So to save time I simply created a sample sheet with a column looking for information in the column beside it (rather than in the other sheet). The actual use is to find something in the next spreadsheet.
Where I erred was in thinking that the entire array was required for the lookup array. When I narrowed it to just the column, it worked.
So thanks for that clarification, and hopefully I have cleared my question up for you.
Gary
Posting Permissions
Reply With Quote
