Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

lookup table


  • Please log in to reply

#1
vally

vally

    Member

  • Member
  • PipPipPip
  • 573 posts
I have a problem with looking up information in an excell table.

I have a table. In one collumn there is a serieal number list. in the other ones are data about this product.
This table was coppied from an access table (if this can affect any thing).
I wrote a vlookup table =VLOOKUP(A3,Wheare house!E:F,2,FALSE)
when some values are ok amd others i get #N\A
If any one knows what how to help or a different answer Id be happy
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Perhaps the value being looked up in "Wheare house!E:F" doesn't exist?
  • 0

#3
vally

vally

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 573 posts
It is in the same file.
I have done this before.


Here is how I used it before with some help from some one here and some personal changes but now I can't get it to work correctly.

=IF(ISNA(VLOOKUP(B63,'חשבונית 2'!F:H,2,FALSE)),"----", (VLOOKUP(B63,'חשבונית 2'!F:H,2,FALSE)))

all I get now is --- when I moved it to the table that I want (with changes).
I tried to play around so I would get the information in file I'm using now, but i get one type correctly and the rest of the information I get N/A
  • 0

#4
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Just tried that formula and it works OK for me :)

Is the value being looked up in column F of the worksheet 'חשבונית 2'?

Is the value you want returned in column G of the worksheet 'חשבונית 2'?
  • 0

#5
vally

vally

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 573 posts
The answer to both questions is yes.

I don't understand why it does not work. :)
Like I posted I have used it before but it is not working :)
The only thing that is different is the data was transfered from an access table. It should not mean anything but ...

Any other ideas?
  • 0

#6
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
If you have been transferring data, make sure the value being looked up is in the same format as the data in the table.

For example, if you are looking up a serial number, say 12345, which is a number in the VLOOKUP formula BUT the 12345 is formatted as text in the table, the formula will return a false. That is, the lookup will not find the 12345.
  • 0

#7
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts

If you have been transferring data, make sure the value being looked up is in the same format as the data in the table.

For example, if you are looking up a serial number, say 12345, which is a number in the VLOOKUP formula BUT the 12345 is formatted as text in the table, the formula will return a false. That is, the lookup will not find the 12345.



Yeah - I've come across this before when copying data out of Access into Excel - what looks like a number is in fact text.

Solution:
Insert a column next to your lookup values, then enter in the formula =Value(A1) (assuming the list starts in A1)
Now copy this all the way down.

Next - copy this list of results and paste VALUES into column A
Finally, delete column B

If this is the problem, the lookup will now work.
  • 0

#8
vally

vally

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 573 posts
Thanx :) Jonesey :)

It worked like a charm!!! :)
  • 0

#9
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Wahey!!

Glad it worked for you.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP