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

[IJR]

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.

 

[JAE]

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.

 

[IJR]

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

 

[IJR]

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

 

[IJR]

Let me correct:

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

 

[nitin36537]

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

 

[IJR]

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

Keep in regular touch.

 

[sc]

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

 

[austim]

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",D14)+SUMIF(C1:C4,"=1",D14).

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

 

[Imagineer]

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

 

[jayz979]

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.

 

[dsi]

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.