How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask Ryan-CTech Your Own Question

Ryan-CTech
Ryan-CTech, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 524
Experience:  Expert VBA Programmer.
18042535
Type Your Microsoft Office Question Here...
Ryan-CTech is online now
A new question is answered every 9 seconds

Hi, I am trying to do a Vlookup and the data being reviewed

Customer Question

Hi, I am trying to do a Vlookup and the data being reviewed is 2 columns wide and 500,000+ rows. I keep getting N/A and do not know what to do. I have 17,000 lines to look up. Please help.

Submitted: 3 years ago.
Category: Microsoft Office
Expert:  Ryan-CTech replied 3 years ago.

yegarboy :

Welcome To Just Answer!

yegarboy :

Just so we are on the same page your data is sorted correct?

yegarboy :

How are you defining the lookup criteria? i.e.. what are you looking up and from where?

JACUSTOMER-xkoxao78- :

Hi

yegarboy :

Hey there :)

JACUSTOMER-xkoxao78- :

Yes A to Z

yegarboy :

Ok thanks.

yegarboy :

And how are you searching?

JACUSTOMER-xkoxao78- :

I am new to vlookup but =vlookup then using the box to enter the data needed

yegarboy :

If you would prefer, you can upload your workbook to me and I can have a look and implement a solution for you.

JACUSTOMER-xkoxao78- :

not sure what you mean by how

JACUSTOMER-xkoxao78- :

well, I am not sure

yegarboy :

I will break it down for you step by step :)

JACUSTOMER-xkoxao78- :

the data contains material I cannot share without permission

JACUSTOMER-xkoxao78- :

wish I could

JACUSTOMER-xkoxao78- :

thank you for trying to help

yegarboy :

Ok,... Lets say Columns A and B is where your data table is. From A1 to B5000.

yegarboy :

Your welcome :)

JACUSTOMER-xkoxao78- :

I have it in c and d

yegarboy :

Ok, what cell are you using to enter in the search data?

JACUSTOMER-xkoxao78- :

order numbers in c and status (return, cancel, shipment in d)

yegarboy :

Ok Im with you so far.

JACUSTOMER-xkoxao78- :

the file were I am trying to pull in the status is 17K lines

JACUSTOMER-xkoxao78- :

with order #'s as well

JACUSTOMER-xkoxao78- :

on a different sheet same file

JACUSTOMER-xkoxao78- :

it has more data but I need to get the status pulled in, matched up by order #

yegarboy :

Right. So you are trying to pull both the Order Number and Status from a Vlookup? or just the status after you enter the number?

JACUSTOMER-xkoxao78- :

orer numbers are in both sheets so status should work fine

JACUSTOMER-xkoxao78- :

order excuse me

yegarboy :

Ok, so do you manually enter an Order number to search for?

JACUSTOMER-xkoxao78- :

I am not sure what your question is asking sorry

yegarboy :

Thats ok :)

yegarboy :

What I mean...

JACUSTOMER-xkoxao78- :

oh I reference the cell

JACUSTOMER-xkoxao78- :

by clicking on it

JACUSTOMER-xkoxao78- :

if thats what you mean

yegarboy :

How do you lookup the Order number? Is it typed into a cell then looks it up 1 by 1?

JACUSTOMER-xkoxao78- :

yes so in the file I am tring to obtain status I have 17K order numbers listed in the furtherest right column.

JACUSTOMER-xkoxao78- :

I think so yes

yegarboy :

Ok, so you want to copy the formula down and have it auto fill the status in

JACUSTOMER-xkoxao78- :

yes

yegarboy :

Ok so sheet1 is the table and sheet2 has the lookup results?

yegarboy :

Just checking :)

JACUSTOMER-xkoxao78- :

nope

yegarboy :

Same sheet?

JACUSTOMER-xkoxao78- :

the lookup is the first sheet

JACUSTOMER-xkoxao78- :

I had a heck of atime just getting the data on the same sheet

JACUSTOMER-xkoxao78- :

does this matter

yegarboy :

I'll bet.

JACUSTOMER-xkoxao78- :

let me switch it and see

yegarboy :

It matters how and where the data is. Thats why i am asking so may questions.

JACUSTOMER-xkoxao78- :

can you give me a sec

yegarboy :

I sure can

JACUSTOMER-xkoxao78- :

I understand, glad you are

yegarboy :

:)

JACUSTOMER-xkoxao78- :

bingo

JACUSTOMER-xkoxao78- :

still a lot of n/a's but I have some data populating

yegarboy :

Ok

JACUSTOMER-xkoxao78- :

I appreciate your help

yegarboy :

Anytime :)

JACUSTOMER-xkoxao78- :

even it cost 65$ :-)

yegarboy :

I will also give you a way to make sure your data stays true

yegarboy :

Make sure you lock in the Table reference with the $ signs in your formula

yegarboy :

Like this

JACUSTOMER-xkoxao78- :

ok great

yegarboy :

=VLOOKUP(E1,$A$1:$B$10,2,TRUE)

JACUSTOMER-xkoxao78- :

k

JACUSTOMER-xkoxao78- :

I thought I needed to put false

yegarboy :

The dollar signs make it so when you copy the formula down, the Table address stays the same :)

JACUSTOMER-xkoxao78- :

This is what I have

JACUSTOMER-xkoxao78- :

=VLOOKUP(L333,'table_data (3)'!C:D,2,FALSE)

JACUSTOMER-xkoxao78- :

can you update the formula?

yegarboy :

Actually lol because you use the entire Column there is no need :)

JACUSTOMER-xkoxao78- :

ok great

JACUSTOMER-xkoxao78- :

can't wait to get this report automated ;-)

yegarboy :

If in the future you want to beef it up let me know :)

yegarboy :

Please dont forget to Rate my help with "Ok" to "Excellent" service :)

JACUSTOMER-xkoxao78- :

will do thanks a lot

yegarboy :

Anytime