×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to do "vlookup" when data is not sorted
4

How to do "vlookup" when data is not sorted

How to do "vlookup" when data is not sorted

(OP)
1. I have data as follows in Excel:

         Joint       Associated Area
           5           500
           2           250
           8           125

Question: vlookup wont work because nothing is sorted, any other function?. No joint data repetition, and I should not sort.

2. Next is a table in Excel:

         Rod     End 1     End2     Length
           1       1        4        5
           2       5        1        12
           3       7        3        3
           4       4        5        2
      
 Rod is sorted, all the rest are not.  Data in end 1 and end 2 can not repeat themselves, ie you can not have end1=1 for rod1 and 2 for example.

Question: What procedure can I use to find the total length of rods with end1=1 and end2=1


Thank you in advance.

RE: How to do "vlookup" when data is not sorted

I'm not totally sure, but I think you can use the INDEX() function along with the MATCH() function to solve both of your problems.  It would look something like:

INDEX(reference,MATCH(),MATCH())

Get into HELP in Excel and read through INDEX and MATCH to see exactly how they work.

RE: How to do "vlookup" when data is not sorted

(OP)
Thanks JAE, I havent tried that yet but a quick eye on HELP and I have a feeling nesting INDEX and MATCH may work.

RE: How to do "vlookup" when data is not sorted

(OP)
JAE, this is how I have used the nesting(for those who might be interested)

      A      B      
 1    3         0
 2    5         1
 3    6      2

I have shown columns A,B and rows 1,2,3 such that B3=1

To retrieve 2 in column B corresponding to 6 in column A,I could not use vlookup since data in A is not sorted. Using your comment I wrote:

 =index(B1:C3,match(6,B1:B3,0),2)

and it worked fine. Thanks

RE: How to do "vlookup" when data is not sorted

(OP)
Let me correct:

I have shown columns A,B and rows 1,2,3 such that cell B3 refers to 2

RE: How to do "vlookup" when data is not sorted

2
vlookup can lookup non sorted data.

For finding value you have to define last optinal parameters as "FALSE".

eg. =vlookup(2,tablerange,2,false) will return 250 as data provided by you.

when we define it as FALSE EXCEL will find coresponding value from the table.

Hope this will work for you.

nitin patel
india



RE: How to do "vlookup" when data is not sorted

(OP)
Thank you patel, I was going to write down that but you are very fast.

Keep in regular touch.

RE: How to do "vlookup" when data is not sorted

Email me for a sample of how I use vlookup. I will send a spreadsheet for simple steel beam design.

It should not matter what the order is providing the data in each row is relevant to the first column of that row. You can also use a cell reference as the lookup value.

sc
peterg@tgmgroup.com

RE: How to do "vlookup" when data is not sorted

Hi, IJR.

By now you may well have forgotten that you ever started this thread, and what follows may be quite irrelevant.  (If so, I offer my apologies).

I have just discovered SUMIF (OK, I may be one of the last on the globe to find it, but it sorted out a stupid little accounting problem for me quite nicely, and I am still quite pleased about that).

Is the correct answer to your second problem =17?  ie = the length of rod 1 (which has end1=1) plus rod 2 (which has end 2=1).  If yes, then I believe that you can do it more simly than trying to use VLOOKUP.  If not, then please ignore all that follows.

Treating your table as being in columns A,B,C,D, then
value required = SUMIF(B1:B4,"=1",D1:D4)+SUMIF(C1:C4,"=1",D1:D4).

(The first SUMIF finds the length (in D) of all rods with end1=1, the second finds all rods with end2=1).

RE: How to do "vlookup" when data is not sorted

Like nitin said you can use the vlookup with the false parameter.  One caveat, this works for EXACT matches.  If you are looking for 11.1 and the data has an entry for 11 and 12 then vlookup will return and error because it didn't find an EXACT match.  The same is true if you look for 11 and the data has an entry for 11.1.


 Imagineer
 
 
 

RE: How to do "vlookup" when data is not sorted

I have a question about VLOOKUP. I am an intern at a pallet production company. I am creating a spread sheet of all the machines and their production. When I created the spreadsheet, I sorted by date. Now my supervisor (who has an industrial engineering degree) wants me to sort these by job and and how long it took to make each job. When I sorted this on a new sheet in excel, my other columns were ruined b/c I had originally made an equation. There were absolute values used, so now my numbers are all screwed up. My supervisor told me that I should probably use VLOOKUP to solve the problem and extract the data. I have read much about this, but still cannot figure out how to set it up. Is there anyone who can help me? I could send an attachment of the file to show what I mean if that would be of assistance to anyone. Someone pleas help me. He says he has an idea how to do it, but wants me to read about it. It has been 3 days now with many trials and I cant figure it out. Thank you to anyone who could help me.

RE: How to do "vlookup" when data is not sorted

You may want to look into a pivot table. They are much easier to read and you won't have to massage your data. The xLOOKUP functions are more for finding target values.

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close