Before the laptop heads for the window could anyone offer some advice on what is probably a simple Excel problem?
I thought the Vlookup function was the answer to my prayers but it appears not. In simple terms what I've got so far in column A are shelf numbers, the next columns have different hand tools which can be found on that particular shelf etc.
Now the bit I'm struggling with, what I want to do is create a function so I type in say "hammer" in one cell and in the next it will tell me which shelf it's on.
I thought I was onto a winner with Vlookup but it appears the search logic is arse about face.
Why did I volunteer for this.......
Thanks
You look up the first column and tell it how many columns to go accross. In this case you have the columns the wrong way round.
Switch description to column A and shelf number to column B.
=VLOOKUP(Question cell,A1:B500,2,false)
Your biggest problem is you would have to type the query in exactly as in list. e.g. Hammer - not hammer
There are possibly a couple of other ways that may suit better.
Last edited by Dazzler; 19th September 2017 at 13:52.
You could experiment with the final value being "TRUE" as opposed to "FALSE" as this will allow for the closest match to your query rather than an exact match.Originally Posted by Dazzler
See here:
https://support.office.com/en-gb/art...a-36c17e53f2ee
Hope that helps
Mark C
The problem might the that you need to move the Tool column (which is the name of tool) to left. Also be careful about the $ signs in VLOOKUP otherwise it will screw if you drag the formula to the lower cells.
Example:
I have a couple of better ways to do it.
On balance I would add a filter to your columns and then use the search box to find all instances of a hammer -it will then hide all relevant lines leaving you with just instances of hammer, claw hammer etc..
A database may be better than a spreadsheet for this task
Sent from my iPhone using Tapatalk
Thanks for all the advice so far, by swapping the columns around I have it working to a certain extent.
What's complicating matters is that each row has many columns of data. So each shelf may have 15 different items split across the columns, the Vlookup only seems to let you search in the left most column.
This is great if whatever I search for is in that column (left most) but if say screwdriver is in column C it's never found.
vlookup should work fine as long there is only one hammer on one shelf
if you share your excel ill do it for you
Not to disagree, but excel makes a very good basis for a database.
Does the spreadsheet contain any sensitive or commercial information? If not send it over and I'd be happy to write some Visual Basic to do exactly what you want, then send it back. It's probably about an hours job and foc clearly!
Have a look at here:
https://exceljet.net/formula/get-add...-lookup-result
I think it is similar to what you want do.
Maybe, but you know what they say, when the only tool you have is a hammer (or pliers).
Have a look at Index Match. Works in a similar manner to a vlookup, but is faster (probably not an issue for you) but also doesn't restrict you to having the columns in a particular order.
http://www.randomwok.com/excel/how-to-use-index-match/
VLOOKUP and HLOOKUP are great once you get your head around the non logical way it works, and it works 3-Dimensionally into other sheets on the same workbook and even across different spreadsheet files
Agreed, using INDEX and MATCH is often a better solution that VLOOKUP once you've figured out how it works!
Posting Permissions
Reply With Quote