Hi
In my file attached I want column L = column K from column M but bring up the value in column N , so basically it should look up the value in column M and bring the SPNAT code up from column N. Hopefully I made sense in what Im saying . Im not good at excel. Im obviously using the vlookup incorrectly.
you are using TRUE - which is nearest value rather than FALSE , exact value
=VLOOKUP(K2,$M$2:$N$140,2,FALSE)
It will return some #N/A when it cannot match
you can use IFERROR to change that result to say a blank
=IFERROR(VLOOKUP(K2,$M$2:$N$140,2,FALSE),"")
Last edited by etaf; 02-23-2021 at 04:55 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
First you need to change the cell format to General from Text so that the formula will actually execute. Then you actually want an exact match, so the last argument should be FALSE not TRUE. You also need to make the lookup table range absolute so that it doesn't adjust as you fill down:
=VLOOKUP(K2,$M$2:$N$140,2,FALSE)
Everyone who confuses correlation and causation ends up dead.
Thank you ETAF
Thank you Rory
Guys thanks for helping me and making sense of this formula
you are welcome
Hi Etaf
Can you tell me If I apply this formula in this same spreadsheet if it will work . Please see Q2
???? sorry dont understand the questionPlease see Q2
Please see the file that I have attached will the formula that you showed me also work here . SEE Q2 WITH FORMULA on the attached spreadsheet.
Do you want me to open up a new thread ?
no will not work as setup
Vlookup - uses the range to left to lookup a value
so
=VLOOKUP(P2,R2:T344,3,FALSE)
as the range starts R - then that is the column you want to look for a match , and then the number is how many rows to the right to go from that R range - Providing the columns are included in the range
so
R2:T344
Looks up in column R and then 1 will return matches in R , 2 will return S and 3 Will return T , but 4 would not work - without changing the range to include that column - so R2:U344
But you seem to be looking in column S
so that needs to be the range and you want the result from column T
so
S2:T344
now as you copy that down, it will change to
S3:T345 etc
to stop that so as you copy down you are still looking in the full range you put a $
so you want
=VLOOKUP(P2,$S$2:$T$344,2,FALSE)
Thank you ETAF , yes I see what you mean about the range that I use.
Thanks once again it has worked.
you are welcome
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks