Searching specific record using VLOOKUP function.

[dev]

My table is not sorted and it has multiple entries for a search criteria. I
want last entry to be picked up by vlookup. Is there any way to do that? Or
can vlookup search from the bottom of the table? This will also solve my
problem.
e.g.
Item Price
it1
it2 32
it3
it1
it4 44
it1 11
it3 20

So, there can be multiple entries for an item but only one entry will have
Price (Price will be blank for all other entries of that item). This entry
will be the last occurrence of that item in the table. I want price of that
occurrence.

 

[Roger Govier]

Hi

Regrettably there is no way to tell Vlookup to start from the end and work
upwards.
If you can accept a VBA solution, the following code will do what you want
(no error checking built in)
It assumes your codes are in column A and Values in column B

Sub FindLast()
Dim rng As Range, code As String, lr As Long, value As Double
lr = Cells(Rows.Count, "A").End(xlUp).Row
code = InputBox("Enter Code to find.")
Set rng = Nothing
Set rng = Range("A1:A" & lr).Find(what:=code, _
After:=Range("A" & lr), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
value = rng.Offset(0, 1).value
MsgBox code & " has a value of " & value
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select sheet containing the PT's
Alt+F8 to bring up Macros
Highlight FindLast
Run

Alternatively you could add a button to your sheet and assign the macro to
it.

 

[dev]

Hi Roger,

Thanks for your help. The code mentioned by you would solve my problem. But
I am not able to customize it for my exact requirement.

Here is the exact requirement.
Following is the sample from a sheet which contains all the transactions:
Date Share Qty Price Paid Txn Total Qty Avg. Price
27-Jun SBI 2 1177.95 buy
27-Jun HDIL 5 429.5 buy
27-Jun spicetele 75 71.8 buy
27-Jun Maruti 2 646.4 buy
27-Jun REL 1 906.15 buy
27-Jun REL 1 895 buy
27-Jun REL 1 885 buy 14 1161.54
27-Jun infosys 1 1700 buy 31 1803.72
27-Jun IFCI 25 40.8 buy
30-Jun LT 1 2229.5 buy 1 2229.5
30-Jun TFCI 60 16.7 buy 530 19.37
30-Jun SAIL 15 139.2 buy 15 139.2
30-Jun Siemens 3 391.9 buy
30-Jun Siemens 1 392 buy 33 729.14
30-Jun IFCI 25 38.8 buy
30-Jun REL 2 815 buy 16 1118.22
30-Jun HDIL 5 391.2 buy
30-Jun HFCL 50 14.95 buy 450 17.43
30-Jun JindalSteel 1 1739 buy
30-Jun REL 2 775 buy 18 1080.08
1-Jul RCOM 3 433.25 buy 12 503.65
1-Jul jp 10 137 buy 1110 134.3
1-Jul JindalSteel 1 1639 buy
1-Jul powergrid 15 72.1 buy 355 58.2
1-Jul IFCI 25 32.9 buy 250 44.08
1-Jul SBI 2 1050 buy 21 1391.96
1-Jul HDIL 5 350 buy 30 466.37
1-Jul JindalSteel 2 1640 buy 11 1837.36
1-Jul Maruti 3 580 buy 96 852.04
1-Jul Pantaloon 6 330 buy 50 526.12


I need to prepare a master sheet which contains each share only once
alongwith its total quantity and average purchase price. Total Quantity and
Average purchase price are determined by the latest entry of the share in the
above sheet.

So, on the basis of above data, my master sheet will be:
Share Total Qty Avg. Price
HDIL 30 466.37
HFCL 450 17.43
IFCI 250 44.08
infosys 31 1803.72
JindalSteel 11 1837.36
jp 1110 134.3
LT 1 2229.5
Maruti 96 852.04
Pantaloon 50 526.12
powergrid 355 58.2
RCOM 12 503.65
REL 14 1161.54
REL 16 1118.22
REL 18 1080.08
SAIL 15 139.2
SBI 21 1391.96
Siemens 33 729.14
TFCI 530 19.37

I can maintain first column of the master sheet manually. Can you provide me
the code to update other 2 columns. I will make a command button on the
master sheet to invoke the code provided by you. This will refresh my data.

I will highly appreciate your help.

Regards,
Dev

 

[Dave Peterson]

With IT1 in A1 of Sheet2 and your data in A1:B999 of sheet1, this returned the
value for the last IT1 in the list:

=LOOKUP(2,1/(Sheet1!A1:A999=A1),Sheet1!B1:B999)

(Make that 999 big enough to extend past the last possible row--but don't use
the whole column.)

 

[dev]

This is working on the sample data mentioned by me. But, this is not working
on the actual data.

Could you please give brief explaination of the value of each argument which
you have passed into the LOOKUP function.

Thanks and Regards,
Dev

 

[Max]

Perhaps one formulas tinker to try out as well ..

Sample data posted is assumed within A1:G31

In I2:
=IF(B2="","",IF(COUNTIF(B$2:B2,B2)>1,"",ROW()))
Leave I1 blank

In J2:
=IF(ROWS($1:1)>COUNT(I:I),"",INDEX(B:B,SMALL(I:I,ROWS($1:1))))

In K2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula:
=IF(ISNA(MATCH(MAX(IF(($B$2:$B$31=$J2)*($A$2:$A$31)*($F$2:$F$31<>""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($A$2:$A$31)*($F$2:$F$31<>""),ROW($A$2:$A$31)),0)),"",INDEX(F$2:F$31,MATCH(MAX(IF(($B$2:$B$31=$J2)*($A$2:$A$31)*($F$2:$F$31<>""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($A$2:$A$31)*($F$2:$F$31<>""),ROW($A$2:$A$31)),0)))
Copy K2 to L2. Select I2:J2, copy down to L31

Col J returns a uniques list of Shares (from col B), while cols K and L
returns the required "from bottom up" results for Total Qty & Avg Price (from
cols F & G)

 

[Max]

Earlier array in K2 seems a little over-roasted
This should suffice:
In K2, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula
=IF(ISNA(MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31<>""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2:$F$31<>""),ROW($A$2:$A$31)),0)),"",INDEX(F$2:F$31,MATCH(MAX(IF(($B$2:$B$31=$J2)*($F$2:$F$31<>""),ROW($A$2:$A$31))),IF(($B$2:$B$31=$J2)*($F$2:$F$31<>""),ROW($A$2:$A$31)),0)))

---

 

[Dave Peterson]

(Sheet1!A1:A999=A1)
will return 999 trues and falses.
1/(Sheet1!A1:A999=A1)

will provide 999
1's or div/0 errors

lookup will not be able to find any 2's in that array so it uses the last
largest number it finds that is smaller than or equal to 2.

That's the last 1 in the array--the last time that sheet2!a1:a999=a1

=index() uses that number to go into column B and return the value.

 

[Dave Peterson]

Sorry, there is no =index() in that formula (I was distracted and lost my train
of thought!).

It just uses that number to return the value in sheet1!b1:b999 for that row.

(ps. change any sheet2's to sheet1's, too.)

 

[dev]

Thanks a lot Max.

It worked for me.

Regards,
Devender

 

[dev]

Dave,

Thanks for your help.

I took Max's solution since it did not involve VB code.

Regards,
Dev

 

[Dave Peterson]

My suggestion didn't involve any VBA.

Dave,

Thanks for your help.

I took Max's solution since it did not involve VB code.

Regards,
Dev

 

[Max]

Welcome. Do press the "Yes" button below, won't you?