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.
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
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
RE: How to do "vlookup" when data is not sorted
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
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
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
Keep in regular touch.
RE: How to do "vlookup" when data is not sorted
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
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
Imagineer
RE: How to do "vlookup" when data is not sorted
RE: How to do "vlookup" when data is not sorted
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.