Can anyone reccomend an online video or step by step tutorial for VLOOKUP please?
I can work using text so pick up 'monkey' 'loves bananas', 'John' 'drives a ford' etc is working but I just cannot get my brain into gear for numbers?
Type a store number '2345' etc into a cell and it returns the store name of 'Tokyo' etc into the cell adjacent on the right is driving me crazy.
Help please :-?
Google contextures vlookup
Entia non sunt multiplicanda sine necessitate
Thanks SHG, Will give it a try :-) There must be one tiny part of it I just don't get when it comes to numbers.Originally Posted by shg
There is no difference between looking up numbers or looking up text - the only thing you need to make sure of, is that the number you are searching for, and the numbers in the data range, really ARE values, and that they are both EXACT.
1.0 <> 1.000000000001
The item you are searching for also MUST be in the 1st column of the vlookup range
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Even more confused now! :-D
I have store numbers in A and names in B
1234 London
2345 Tokyo
3456 New York
4567 Berlin
Etc but I still just end up banging my head against a brick wall :-(
Thought it was because I had the store numbers/names to the right of the columns on my sheet or that I had them on another sheet entirely but it still baffles me.... "Can't see the wood for the trees" comes to mind.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Are you numeric values formatted as text? Do they contain leading or trailing spaces? Rogue spaces are often the culprit when VLOOKUPs that look like they should work don't. Your lookup_value and the array at which it is looking have to be EXACTLY THE SAME for a lookup to work, i.e.: "1234" and "1234 " (note the space after the 4 in the latter) are NOT matches insofar as a VLOOKUP goes.
I hope this makes sense??
My plan was to have the list of store numbers/names on a seperate sheet so there was no chance of it being corrupted if someone adds columns etc.
Re: VLOOKUP Tutorial?
is that what you want?
I prefer index/match
if you want store numbers/names on a seperate sheet so simply change referenceFormula:
Please Login or Register to view this content.
Last edited by sandy666; 06-13-2018 at 06:13 PM.
Exactly what I want Sandy! So where is the formula for that on the sheet so I can try to understand it and play with it on a new sheet?
formula is there where result is
Do i always have to put the number and name reference to the left of the columns I want to display??
Re: VLOOKUP Tutorial?
I don't understand.
You want put the number (ID or something) to the first cell and in next cell you want to return name associated to this number
you can put the number anywhere but you'll need change reference lookup_value from MATCH function just to this cell where you put your number
see attached file, but layout like this doesn't make any sense. this is example only
Last edited by sandy666; 06-13-2018 at 06:46 PM.
I have worked out how to add more numbers/stores to column A and B and to pick them up in F5 but how do I add rows int F6, F7, F8 etc?
Re: VLOOKUP Tutorial?
It's depend what you want to achieve. Change your source to the Excel Table then add what you want
Change formula to =IFNA(INDEX($B$2:$B$8,MATCH($F5,$A$2:$A$8,0)),"") without $ sign and drag down
then type the number in column F and you'll see result in column G
I think you should read this: INDEX & MATCH in Excel - a better alternative to VLOOKUP
there is more different situations to learn
Read me
Try to avoid joining to my Black List by doing this below
If that takes care of your original question, & to say Thanks and for better Motivation, pleaseIf you did it already - ignore it.
- click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then- select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
Thank you.
Last edited by sandy666; 06-13-2018 at 07:32 PM.
My brain is minced...... this might as well be a recipe for bread for any sense its making :-(I think you should read this: INDEX & MATCH in Excel - a better alternative to VLOOKUP
there is more different situations to learn
Read me
Try to avoid joining to my Black List by doing this below
If that takes care of your original question, & to say Thanks and for better Motivation, pleaseIf you did it already - ignore it.
- click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then- select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
Thank you.
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding
For normal conversational replies, try using the QUICK REPLY box below.
but where is the problem?
Its just me Sandy, my poor old brain isn't getting this for some reason :-?
Ok then, I posted links to other sites with tutorials (simplest I hope) which you can read with coffee break
Have been on the tea, will have a coffee, give my head a shake and try again :-D
Noooooooooooo! :-?
I copided your formula into my full sheet, changed the cell and copied it down. It worked so i copied it into the other two columns for store number/names and now nothing works
Re: VLOOKUP Tutorial?
Check references or attach desensitized workbook with reflected your original type of data and place where they are
check if your input exist in your source table
here is example with two sheets
source on sheet1
result on sheet2
see references in formula
Last edited by sandy666; 06-13-2018 at 08:36 PM.
Copied the info from coluns A and B to same position on my main sheet. Changed your formula to column L. It worked ok then when I went back to it i got a column of '0'
post#28 was updated so re-read this
sorry my friend but 2:30 am here and I don't want my keyboard to be reflected on my face so I am out of here.
cya next time
Last edited by sandy666; 06-13-2018 at 08:41 PM.
Thank you Sandy. Will try again when fuelled by caffeine :-)
You are welcome
If you have any questions - post them and I'll read them later
This is an interactive training excel file by benishiryo for vlookup, maybe it is of some use to you.
However, if you are almost there - continue with sandy as index match is more flexible and you seemed to suggest you might want to have the number and name reference the other way around.
If you want something done right... find a forum and ask an online expert.
Time flies like an arrow. Fruit flies like a banana.
Thank you everyone....... I have it now. The power of a cheese and onion sandwich should never be underestimated!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks