hi all
i have a vlookup sum that is looking at a list of UK postcodes and then shows the data in the 2 columns next to it, its providing the right result some times but sometimes it seems to be chosing the cell above my result.
here are my 2 sums please let me know if there is anything wrong here or a better way of doing it
=VLOOKUP(B28,sheet4!A2:E10579,2)
=VLOOKUP(B28,sheet4!A2:E10579,3)
kind regards
rick
Try these:
=VLOOKUP(B28,sheet4!A2:E10579,2,0)
=VLOOKUP(B28,sheet4!A2:E10579,3,0)
Hope this helps.
Pete
hi mate this is not working and producing a NA i have attached an example please have a go guys! test vlookup.zip
Please try this.
A13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!A$2:A$10579)
D13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!B$2:B$10579)
G13 =LOOKUP(2^15,1/(SEARCH($E$7,Sheet2!$A$2:$A$10579)),Sheet2!C$2:C$10579)
Please click 'Add reputation', if my answer helped you.
Please check you PostCode database..
In PostCode area.. approx all Postcode's contain.. 4-5 spaces..
Remove them all.. Pete_UK's suggestion will work for you..
To remove spaces.. in a separate cell use formula as Trim(A2) and drag toward down..
Now Copy and paste - values in original location..
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
hi,
The main problem for producing a wrong result is due to the one less space.Hence in sheet1 eh9 give 1 more space so that its length will be 10 as currently it is 9.As in Sheet 2 the character counts of EH9 2 is 10 that leads to mismatch and produce error.This is perhaps due to your typo error.After giving one more space,use below formula-
=VLOOKUP(E7,Sheet2!$A$2:$E$10579,3,FALSE)
Hope this will solve your problem.
With Best Rgds,
suresh
=VLOOKUP(B28,sheet4!A2:E10579,2,false)
=VLOOKUP(B28,sheet4!A2:E10579,3,false)
now to fix column A of sheet 2 to remove trailing spaces
select col A
go to data tab or if you have 2003 or earlier just click data on the tool bar /text to columns/fixed width just click finish that should get rid of all those trailing spaces
Last edited by martindwilson; 09-05-2013 at 08:25 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks