Another Vlookup Query...

D

dariuzthepole

Board Regular
Joined
Jul 23, 2008
Messages
111
Hi all,

Firstly, I have looked at the pages regarding my issue, but none seem to fit (please correct me if I'm wrong).

I have a table, a list of properties I am considering renting. Outside of the table, I have referenced the cheapest and the most expensive properties that are in my table.

This works fine, except when there are two properties that cost the same. Where a lot of the Vlookup questions I have read ask to return the 2nd/3rd value, I want to show them all, eg. if two properties of equal value are the dearest, I'd like to reference both, not just the 1st or 2nd.

Any help/advice you can give me would be greatly appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Not knowing how it looks in your sheet. But there are possibilities.
Pivottable may work. Also, you could use a helper column, then use RANK to rank the prices from high to low and return Rank1 and rank2

Maybe you could post an example?
 
Upvote 0
<TABLE style="WIDTH: 173pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=230 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1632" width=51><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2496" width=78><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1568" width=49><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1664" width=52><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl68 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 38pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=51 height=19>£1,133</TD><TD class=xl65 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 59pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=78>Lancefield Quay</TD><TD class=xl75 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 37pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" width=49>1</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #e0dfe3; WIDTH: 39pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right width=52>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,024</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Barrland Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">2</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£25</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£974</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Paisley Road West</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">3</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£40</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,158</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">High Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">4</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,049</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Bell Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£24</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,054</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Titwood Road</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">6</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£32</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,054</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Moir Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">7</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,154</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">West Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">8</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,099</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Mavisbank Gardens</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">9</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,183</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Turnbull Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">10</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£945</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Lorne Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">11</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£40</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,099</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Prospecthill Grove</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">12</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£40</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,183</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Lancefield Quay</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">13</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl69 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,064</TD><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">Lymburn Street</TD><TD class=xl71 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent">14</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl70 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>£1,079</TD><TD class=xl66 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Riverview Gardens</TD><TD class=xl72 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">15</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>£0</TD></TR></TBODY></TABLE>

This is one of the reference tables I have. I have a table where data is entered and the figure above is totalled. The 1-15 numbers are the number of the row in the table and not any sorting/ranking system.

I have MINIMUM & MAXIMUM reference cells which vlookup this table and extracts relevant data. In this example, 10 & 13 are the most expensive, however because I am using a MAXIMUM function, it is only referencing one of the maximum figures, not both.
 
Upvote 0
Why not first find the min and max, assign each to a variable and then check each value in your list against those variables in a loop?
 
Upvote 0
Top one was wrong, you need unique values, the previous usage of rank would show you multiple "2" ranks.

This version works with unique ranking.

Excel Workbook
ABCDEFG
1RankPriceStreetNumberValueHighest Top 3
251133Lancefield Quay101Turnbull Street
3131024Barrland Street2252Lancefield Quay
414974Paisley Road West3403High Street
531158High Street40
6121049Bell Street524
7101054Titwood Road632
8111054Moir Street70
941154West Street80
1061099Mavisbank Gardens90
1111183Turnbull Street100
1215945Lorne Street1140
1371099Prospecthill Grove1240
1421183Lancefield Quay130
1591064Lymburn Street140
1681079Riverview Gardens150
Sheet3
 
Upvote 0
shyrath said:
Top one was wrong, you need unique values, the previous usage of rank would show you multiple "2" ranks.

This version works with unique ranking.

Excel Workbook
ABCDEFG
1RankPriceStreetNumberValueHighest Top 3
251133Lancefield Quay101Turnbull Street
3131024Barrland Street2252Lancefield Quay
414974Paisley Road West3403High Street
531158High Street40
6121049Bell Street524
7101054Titwood Road632
8111054Moir Street70
941154West Street80
1061099Mavisbank Gardens90
1111183Turnbull Street100
1215945Lorne Street1140
1371099Prospecthill Grove1240
1421183Lancefield Quay130
1591064Lymburn Street140
1681079Riverview Gardens150
Sheet3
Click to expand...

------------------------------------------------------------
I am following what have there, but Excel does not like the COUNTIF function & will not accept it. This is what I am trying to enter in cell L3:

=RANK(J3,$J$3:$J$17,0)+countif($J$3:J3;J3)-1

where J3:J17 is the range of total figures. The red J3 above is the item Excel is highlighting after the error warning message.
 
Upvote 0
Yes that works now, thanks,

Is there a way to invert the ranking system? At present, the most expensive is no.1, and the least expensive is no.15. In reality, the cheapest is most desirable, therefore should be no.1.

Also, how do I now reference both most expensive properties in one cell? Can it be done?
 
Upvote 0
Yes, you can re-sort by changing the 0 in below to a 1
Rich (BB code): 
=RANK(J3,$J$3:$J$17,0)

as for combining them, yes, should be possible.

Try something like:
Rich (BB code): 
=VLOOKUP(1,$A$1:$E$16,3,FALSE)&"-"&VLOOKUP(1,$A$1:$E$16,3,FALSE)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Complex Lookup issue
Replies
1
Views
144
arthurbr
arthurbr
S
  • Question
Query on Macro
Replies
0
Views
140
sksanjeev786
S
N
  • Solved
Formula to find the in-between number in a range
Replies
2
Views
160
Fluff
Fluff
B
  • Question
Data Table: Goal Seek Multiple Criteria
Replies
0
Views
299
bsps
B
S
  • Solved
IF Statements for Dates with VLOOKUP and Concatenate
Replies
3
Views
827
Tetra201
Tetra201

Forum statistics

Threads
1,214,545
Messages
6,120,132
Members
448,947
Latest member
test111

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top