LinkBack URL
About LinkBacks
any help?
Example
Sheet1
A B C D E F G H I J
1 save adr100
2 all adr20
Sheet2
A B
adr100 =vlookup(a1,Sheet1!A1:J3;2;False)
The result of =vlookup = #NA that supposedly to be 'save' any help?
any help?
Example
Sheet1
A B C D E F G H I J
1 save adr100
2 all adr20
Sheet2
A B
adr100 =vlookup(a1,Sheet1!A1:J3;2;False)
The result of =vlookup = #NA that supposedly to be 'save' any help?
Last edited by VBA Noob; 01-31-2009 at 10:11 AM.
The data to search for must be the same as the data in the table
XL2007 has an Iferror Function
I don't use XL'07 but I have seen it.
Check out the help file for IfError
Here's a site as well
http://exceltip.com/st/IFERROR_Funct...007_/1372.html
Last edited by davesexcel; 01-31-2009 at 09:46 AM.
It's impossible to tell your real data layout from what you have posted.
Can you attach a workbook example.
If you use the formula
=vlookup(a1,Sheet1!A1:J3;2;False)
then that searches for A1 in sheet1 A1:A3 and, if found, returns from the corresponding row in sheet1 B1:B3. From your example it looks like you want to return a value from a column to the left of the lookup range, you can't do that with VLOOKUP, try using an INDEX/MATCH construction, e.g. to look up the value in B1:B3 and return a value from A1:A3 use
=INDEX(sheet1!A1:A3,MATCH(A1,sheet1!B1:B3,0))
see here for some explanation of INDEX/MATCH
I upload my workbook sample. I save it using Excel 2003 Format because i think out there still a lot of people using 2003 Format. Thanks
it's my first time to upload file here. I don't know it's work or not.
I don't see it....Originally Posted by gasukadingin
Ok i think it's failed to upload. I don't know why. Maybe it's due to my super slow internet connectivity. I will try again soon.
The workbook may be too large, just make it small enough for a sample.
Nope my workbook only 17 kb, it's just my internet problem
It's success to upload now
Ok need help for this strange formula that it work on 2003 but not at 2007
That formula does not work in either version.
daddylonglegs explained how the VLOOKUP formula works and your data is not laid out in a way that will allow you to use VLOOKUP.
=IF(ISERROR(MATCH(A3,Sheet1!J3:J5,FALSE)),"",INDEX(Sheet1!B3:B5,MATCH(Sheet2!A3,Sheet1!J3:J5,FALSE),1))
Ok thanks for your help, i will try that tips.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks