I thought I was getting the hang of vlookups, but it seems not.
I have four columns with six digit figures in each, two with 205 rows and two with 506.
I am using this in column e to find a match:-
VLOOKUP($B$2:$B$507,$D$2:$D$507,1,FALSE)
All I see is #N/A, rather sadly.
Clearly easy to resolve, just not to me...
John.
Last edited by Johnmitch93; 01-26-2012 at 05:57 PM.
The first parameter is VLOOKUP is a single lookup value.
=VLOOKUP(ValueToFind, TableToFindValueIn, ColumnInTableToReturnValueFrom, False/True)
This formula would locate the value in A1 in column B, then return the value from column D:
=VLOOKUP(A1, $B:$D, 3, True)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
That is returning some value which are not in column A.
I have simplified this down to two columns.
Simple usually works for me.
Attached is where I have got to so far, which is not far to be honest.
John.
John, couple of things. Columna A is not taken as number (at least in my PC). Step in cell A1, press F2 and hit enter, then press F2 again and enter, repeat this many times. You will se how it works.
Other thing: you will probably want the "TableToFindValueIn" be always the same: to do so in formula in cell A1 select B1:B505 part and hit F4 key so it becomes $B$3:$B$507, Wen you drag the formula, the reference will stay the same.
Hope this helps
What is the answer you EXPECT to appear in C1? In C2?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
OK - I'll have a go at that.
Iam expecting the lookup to display the number from column A when it finds the same number in column B.
John.
If you want a list of the numbers from A that also exist in B, then VLOOKUP is the wrong formula.
Put this formula in C1:
=IF(ISNUMBER(MATCH(A1+0,B:B, 0)), 1, 0)
Put this formula in C2, copy down through C205:
=IF(ISNUMBER(MATCH(A2+0,B:B, 0)), C1+1, C1)
Then put this formula in E1 and copy down until you start getting errors... that will be your matching set:
=INDEX(A:A, MATCH(ROW(A1),C:C, 0))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks mate.
I'll see how I get on and let you know.
John.
Ah!
The first one gives a count of the matches. That's cool.
John.
Just a tip, thread "titles" should be briefly descriptive of your problem, not a guess at the answer. "List of numbers that match a second list"....something like that.
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. While your there you could update the thread title, too, for posterity.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks mate,
John.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks