I have simple vlookup formula:
The table is from A1:C5
Please Login or Register to view this content.
How can the formula be amended to do the following...
When it returns the value of column 2, how can I also return the 3rd value along and combine the 2 columns together as 1 value?
Easy peasy:
=VLOOKUP(E1,A1:C5,2,FALSE)&VLOOKUP(E1,A1:C5,3,FALSE)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks once again ahaha!
Any more?
You are welcome.
Not just yet ahaha
Until the next time, then. LOL!
Going back to this problem, I explained it wrong. Instead of searching the first value and combining the other 2 to get the final result...
I need to combine 2 values together and use that combined value to search. I have attached a better sample workbook.
Hope this makes sense..
In P4 copied down:
=VLOOKUP(VALUE(M4&N4),M$11:M$15,1,0)
Where did you learn excel?...
Just by wanting to learn. I've been learning for nearly thirty years. I am a teacher, and as a Head of Department I have to analyse test data by converting grades to values and back again - that's how it started. Initially I shared ideas with other colleagues who were also learning - we liked to compete with each other for the most efficient formula! Lots of time spent helping here since 2013 has honed my skills. I keep a OneNote notebook full of tips that I've learnt from my time here.
Last edited by AliGW; 02-19-2019 at 07:19 AM.
How you got time on here as a teacher? And have you tried doing programming?
I have Sundays and holidays. I am on half term now.
I don't do VBA, although I use a few simple routines that others here have helped me with. My big love at the moment is Power Query: I am also now responsible for the entire school's data analysis, and I use PQ to normalise data ready to analyse in Power BI. It's great fun! You never stop learning.
I teach languages, by the way.
Don't forget to mark this as solved.
Whats Power Query ? And im trying to compare something, its beyond my excel knowledge but I try to improvise questions on here to get the final result. But obviously learning from it...
As long as you learn from it, then it's fine to ask, of course - that's part of it.
Power Query: https://support.office.com/en-us/art...5-89f6269cd605
Would you mind sharing it ? ..Originally Posted by AliGW
I keep a OneNote notebook full of tips that I've learnt from my time here.
☚ Click ★ just below left if it helps, Boo?ath?
Last one I hope...
How would I reverse the formula? Instead of searching from Table 1 search from table 2...
Yes, I would mind - no, I won't share it, sorry - it's personal and far too much of a mess to make any sense to anyone else, anyway, (it's rather like a digital scrapbook) but you can easily start your own. When you copy and paste ideas from forums, it automatically saves the link, so it becomes a compendium of ideas. I have tabs for different functions. Hope this helps!Would you mind sharing it ? ..
Not sure what you mean - can you provide an example?
E.g. search 12 from table 2 and return the value of table 1. Keep in mind the digits from table 1 are double digits not single as they are suppose to be combined
Do you mean this?
=VALUE(VLOOKUP(--LEFT(M11,1),M$4:N$8,1,0)&VLOOKUP(--LEFT(M11,1),M$4:N$8,2,0))=M11
Excel 2016 (Windows) 32 bit
EXPECTED Table 1 1 2 2 5 3 7 4 9 5 0 Table 2 12 25 37 49 57
Sheet: Sheet1
Yes that's perfect! Reckon you could explain the formula please?
Yes!
=VALUE(VLOOKUP(--LEFT(M11,1),M$4:N$8,1,0)&VLOOKUP(--LEFT(M11,1),M$4:N$8,2,0))=M11
--LEFT(M11,1)
This looks at the first character (digit) in M11 (which is the leftmost) and using the double-unury -- converts it to a numerical value.
VLOOKUP(--LEFT(M11,1),M$4:N$8,1,0)
This searches for --LEFT(M11,1) (which is 1) in the first column of the range M$4:N$8 and returns what it finds in that column(1).
VLOOKUP(--LEFT(M11,1),M$4:N$8,2,0)
This searches for --LEFT(M11,1) (which is 1) in the first column of the range M$4:N$8 and returns what it finds in the second column (2).
&
This concatenates the two values (1 & 2 = 12).
VALUE(...)
This converts the text string (12) into a proper numerical value.
=M11
Confirms that what is M11 exists in the lookup range and returns TRUE or FALSE.
Intresting, I have just tried this formula on the following table. Where Im I going wrong?
Please Login or Register to view this content.
Not sure what you were trying to do!!!
Excel 2016 (Windows) 32 bit
Table 1 501335102286 501335102287 501335102288 501335102289 501335102290 Table 2 5013351022862 5013351022879 5013351022886 5013351022893 5013351042259
Sheet: Sheet1
Excel 2016 (Windows) 32 bit
Sheet: Sheet1
Did this help?
Sorry was out for lunch...
Table 1 Column A is 12 digit barcode and in column B is the check digit for it.
Table 2 is a full barcode of 13 digits with a check digit combined.
Im comparing the two if they match or not.
You might be thinking of usingBut id prefer it in one formulaPlease Login or Register to view this content.
That's what my formula in post #25 does.
Why might I be thinking of using that? And how is that not one formula?You might be thinking of using
=A2&B2
Please mock up a fresh workbook with everything you want in it. Make it very clear which you consider to be source cells (already in situ) and which are results (i.e. require a formula to produce their result). All mocked up manually, please - no attempted formulae, as that confuses the issue.
The constant activity is a solved thread caught my attention, curiosity got the better of me.
Is this what you were trying to do?
=IFERROR(VLOOKUP(VALUE(LEFT(A9,12)),$A$2:$B$6,2,0)=VALUE(RIGHT(A9,1)),"No Match")
That's essentially what my last formula does, just in a slightly different way.
..........
Hi Jason, thats exactly what I needed. Thanks
So explain how my formula didn't do what you want? It does the same thing essentially.
When I pasted the formula in, it gave false value. Whereas Jasons gave what I needed. Im not sure where its going wrong. Im gonna have a look now. Are you referring to formula based inside post #25?
Ali, yours returns false when it should be true, perhaps without the value function...
Yes, and it is created to return TRUE or FALSE, as explained in post #23. Anyway, never mind - you have got what you need.
The goalposts kept shifting, Jason - I must have lost the thread somewhere along the way.
Sorry if im confusing anyone here haha, thanks for your help both of you especially Ali
That's the main thing. Glad to have helped to some extent.
One last one for this thread...
The formula Jason has gave me. How would I add in a "0" before the value. So it just adds on a extra digit before the number
Maybe like this:
=IFERROR("0"&VLOOKUP(VALUE(LEFT(A9,12)),$A$2:$B$6,2,0)=VALUE(RIGHT(A9,1)),"No Match")
Im trying to prefix an extra zero for table 1 without adjusting the table itself. I have tried the formula you gave me with "0".
Try this:
=IFERROR(VLOOKUP(VALUE(MID(A9,2,12)),$A$2:$B$6,2,0)=VALUE(RIGHT(A9,1)),"No Match")
What does the MID do for this current situation? I didn't see a "0"?
MID(A9,2,12)
Looks at A9 - starts at the second character (5) and looks at 12 characters from there, so it's looking at this bit of the number (in red): 05013351022862
I was looking for this few ago ahaha! Even better
If you have any further queries, please start a new thread. This one has veered massively off course!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks