Excel VLOOKUP

[strikinglyfamiliar]

In my original thread I had a database that was only about 1600 lines. The orginal file worked fine when I made the suggested correction of converting the column to numbers. I now have a much larger database to work with, consisting of over 49000 lines. I need to confine the array to be between, Sheet2!A2:Sheet2!D2649.

I am listing two lines, the first works, the second does not, They both gave the required result in the first file.

Works:
=VLOOKUP($A2,Sheet2!A2:Sheet2!D2649,2,FALSE)

Result: Cover Assy, Tank, Solution Fill

Doesn't work:
=VLOOKUP($A13799,Sheet2!A13799:Sheet2!D16446,2,FALSE)

Desired Result: FAN, VACUUM, 5.1D, 120VAC, 1 STAGE [TF]
Result given: #N/A

I notice that the array searches progressively, thus searching for data where there would be non. How can I restrict this search?

[FDibbins]

vlookup() only takes 1 array, you have a single cell ref at the start (A13799) what are you trying to do with that? try and remove it and see if it works then

edit: umm duh, sorry. brain freeze (my only excuse is that i just woke up) sorry.
maybe its because the cell you are searching for is inside the range you are looking at?

[bonny24tycoon]

Hi There,

If the data you need is just between Sheet2!A2:Sheet2!D2649 then why dont try this and drag it till your requirements. The "$" sign before the column name and row number would fix the column and row of your search

Please Login or Register  to view this content.

drag it till your requirements

[strikinglyfamiliar]

I have one page of data that is a complete list. I then have a second page of data that is the available list. I need list A to pull the matching data from list B, and make one row of correlated information.

[ChemistB]

From what you've described, Bonny's solution should work for you. Can you upload a dummy spreadsheet showing exactly what you are doing and what you expect? (Go Advanced>Manage Attachments)

[strikinglyfamiliar]

I have tried the $ correction suggested, but the problem is that the search progresses column and line past the area where the data is located. Say the data in sheet 1 is listed at A13799, and in sheet 2 that same data is located at A1650, the VLOOKUP (as it progresses from A3 down only searches accordingly on sheet 2) SO, the data I am searching for, is before where the VLOOKUP is looking at sheet 2. I need my search to be restricted to just one area, and not grow with every line in sheet 1.

[bonny24tycoon]

I believe we all would need an example spreadsheet to understand and fix your issue.

Can you follow ChemistB steps and upload your file. Thanks

[strikinglyfamiliar]

I have attached part of the file. The whole file is too large to upload.

[bonny24tycoon]

So does the partial file mean that it would search A1 in column M and should display the corresponding value in column N?

Would you run the formula on sheet1 or Sheet2.

I would suggest send an example sheet the way it looks (example: some data on Sheet1 and some on Sheet2, where would you run the formula and some comments)

Its a bit hard to interpret your query with your above posts.

[strikinglyfamiliar]

To begin, sorry about the upload. When I copied part of the file, pasted in a new one to make it short enough to upload is screwed it all up.

The term partial was used because I was limited to the size of file that I could upload, and I didn't want it confused with what I am working on.
Realize that the file is 49000 lines long on sheet 1

Sheet 2 contains the data I want to search

Cell K2 of the file I am working on contains the formula =VLOOKUP(A2,Sheet2!$A2:Sheet2!$D2649,2,FALSE)
This returns the desired answer

random cell in column K shows a formula of =VLOOKUP($A57,Sheet2!$A57:Sheet2!$D2704,2,FALSE)
Which, you can see, no longer begins it's search at sheet2!A2
It Begins the search at sheet2!A57

I need the search to be confined, and not grow progressively so that the search perameters are kept, even at line 48999

I have fixed a couple of lines close to that limit, and refined the array to reflect the data I want searched, and got the answer I desired, but if I copy/paste the formula to the entire column, cell by cell, it changes.

Example of what worked:
=VLOOKUP($A13799,Sheet2!$A2:Sheet2!$D2649,2,FALSE)

Returned the description that matched

HOWEVER, the line above it was not corrected, and did NOT return a result -

Example
=VLOOKUP($A13798,Sheet2!$A13798:Sheet2!$D16445,2,FALSE)


So, do I go line by line, 49000 times and correct the search array?

[ChemistB]

What you need to do, in K2, the first formula is anchor the rows.

You have Sheet2!$A2:Sheet2!$D2649,
what you want is
Sheet2!$A$2:Sheet2!$D$2649, then when you drag, that range will stay constant.
As an aside, you also don't need the second "Sheet2!"
=VLOOKUP(A2,Sheet2!$A$2:$D$2649,2,FALSE)
Hope that helps.

[strikinglyfamiliar]

Thank You ChemistB THAT is the answer... I was wanting to anchor the search. Once that was done, the appropriate data matched, and I could move to the next column making minor changes, and the formula continued to work. Now when I get my next three databases, I should be able to make it all match.

Thank You Bonny, and ChemistB for helping me understand the subtle changes I needed to make, for this to work.