Find matching value in large table and return associated data

S

Sirod

New Member
Joined
Aug 6, 2009
Messages
47
Hello,
I have a large spread sheet that lists multiple currencyvalues. For each value there is anassociated daily fee, weekly fee, and monthly fee.
For example:


[TABLE="width: 645"]
<tbody>[TR]
[TD="width: 67, bgcolor: #BDD7EE"]Value
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Daily Rate
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Weekly Rate
[/TD]
[TD="width: 81, bgcolor: #BDD7EE"] Monthly Rate

[/TD]
[TD="width: 67, bgcolor: #BDD7EE"]Value
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Daily Rate
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Weekly Rate
[/TD]
[TD="width: 81, bgcolor: #BDD7EE"] Monthly Rate
[/TD]
[TD="width: 67, bgcolor: #BDD7EE"]Value
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Daily Rate
[/TD]
[TD="width: 69, bgcolor: #BDD7EE"] Weekly Rate
[/TD]
[TD="width: 81, bgcolor: #BDD7EE"] Monthly Rate
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]$3,050
[/TD]
[TD="bgcolor: transparent"] $ 4.27
[/TD]
[TD="bgcolor: transparent"] $ 71.36
[/TD]
[TD="bgcolor: transparent"] $ 45.43
[/TD]
[TD="bgcolor: #BDD7EE"]$7,202
[/TD]
[TD="bgcolor: transparent"] $ 6.60
[/TD]
[TD="bgcolor: transparent"] $ 83.02
[/TD]
[TD="bgcolor: transparent"] $ 96.14
[/TD]
[TD="bgcolor: #BDD7EE"]$1,355
[/TD]
[TD="bgcolor: transparent"] $ 8.94
[/TD]
[TD="bgcolor: transparent"] $ 94.68
[/TD]
[TD="bgcolor: transparent"] $ 46.86
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]$5,506
[/TD]
[TD="bgcolor: transparent"] $ 0.04
[/TD]
[TD="bgcolor: transparent"] $ 50.18
[/TD]
[TD="bgcolor: transparent"] $ 53.29
[/TD]
[TD="bgcolor: #BDD7EE"]$9,281
[/TD]
[TD="bgcolor: transparent"] $ 2.16
[/TD]
[TD="bgcolor: transparent"] $ 60.78

[/TD]
[TD="bgcolor: transparent"] $ 99.40
[/TD]
[TD="bgcolor: #BDD7EE"]$3,056
[/TD]
[TD="bgcolor: transparent"] $ 4.28
[/TD]
[TD="bgcolor: transparent"] $ 71.38
[/TD]
[TD="bgcolor: transparent"] $ 45.50
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]$8,642
[/TD]
[TD="bgcolor: transparent"] $ 6.18
[/TD]
[TD="bgcolor: transparent"] $ 30.91
[/TD]
[TD="bgcolor: transparent"] $ 69.46
[/TD]
[TD="bgcolor: #BDD7EE"]$2,075
[/TD]
[TD="bgcolor: transparent"] $ 8.11
[/TD]
[TD="bgcolor: transparent"] $ 40.55
[/TD]
[TD="bgcolor: transparent"] $ 11.39
[/TD]
[TD="bgcolor: #BDD7EE"]$5,507
[/TD]
[TD="bgcolor: transparent"] $ 0.04
[/TD]
[TD="bgcolor: transparent"] $ 50.18
[/TD]
[TD="bgcolor: transparent"] $ 53.30
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]$2,374
[/TD]
[TD="bgcolor: transparent"] $ 2.66
[/TD]
[TD="bgcolor: transparent"] $ 13.31
[/TD]
[TD="bgcolor: transparent"] $ 92.90
[/TD]
[TD="bgcolor: #BDD7EE"]$5,493
[/TD]
[TD="bgcolor: transparent"] $ 4.41
[/TD]
[TD="bgcolor: transparent"] $ 22.07
[/TD]
[TD="bgcolor: transparent"] $ 31.00
[/TD]
[TD="bgcolor: #BDD7EE"]$8,612
[/TD]
[TD="bgcolor: transparent"] $ 6.17
[/TD]
[TD="bgcolor: transparent"] $ 30.83
[/TD]
[TD="bgcolor: transparent"] $ 69.09
[/TD]
[/TR]
[TR]
[TD="bgcolor: #BDD7EE"]$6,706
[/TD]
[TD="bgcolor: transparent"] $ 9.63
[/TD]
[TD="bgcolor: transparent"] $ 8.15
[/TD]
[TD="bgcolor: transparent"] $ 26.93
[/TD]
[TD="bgcolor: #BDD7EE"]$9,541
[/TD]
[TD="bgcolor: transparent"] $ 1.07
[/TD]
[TD="bgcolor: transparent"] $ 5.36
[/TD]
[TD="bgcolor: transparent"] $ 58.30
[/TD]
[TD="bgcolor: #BDD7EE"]$2,376
[/TD]
[TD="bgcolor: transparent"] $ 2.66
[/TD]
[TD="bgcolor: transparent"] $ 13.32
[/TD]
[TD="bgcolor: transparent"] $ 92.93
[/TD]
[/TR]
</tbody>[/TABLE]


In a separate location I want to be able to manually enter aspecific value and have excel search the table to find that value and thenreturn the applicable daily fee, weekly fee, and monthly fee. For example:


[TABLE="width: 311"]
<tbody>[TR]
[TD="width: 94, bgcolor: transparent"]Value:
[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]$2,075
[/TD]
[TD="width: 256, bgcolor: transparent, colspan: 4"]<- this value will be manually entered
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Daily Rate:
[/TD]
[TD="bgcolor: transparent"]$8.11
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Weekly Rate:
[/TD]
[TD="bgcolor: transparent"]$40.55
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Monthly Rate:
[/TD]
[TD="bgcolor: transparent"]$11.39
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


It is not an option to simply combine the different valueand fee columns into one for each. I amunable to make changes to the table listing the values and fees.

Please no VBA or array formulas.

Thank you,

S




 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
If you are willing to try a macro, it can be very easily done. All you would have to do is enter a specific value in a cell and press the RETURN key. If you are willing to try, I would need to know the cell where you will enter a specific value. Also, is there a possibility that the specific value has more than one occurrence?
 
Last edited:
Upvote 0
Are there only 3 different columns for the Value? If yes then you can use this nested iferror index match combination: (I have assumed the data to be in cells A1:L6 - from the table you created in initial post, change ranges accordingly):

Daily Rate:
IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$A$1:$A$6,FALSE),2),IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$E$1:$E$6,FALSE),6),INDEX($A$1:$L$6,MATCH($P$2,$I$1:$I$6,FALSE),10)))

Weekly Rate:
IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$A$1:$A$6,FALSE),3),IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$E$1:$E$6,FALSE),7),INDEX($A$1:$L$6,MATCH($P$2,$I$1:$I$6,FALSE),11)))

Monthly Rate:
IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$A$1:$A$6,FALSE),4),IFERROR(INDEX($A$1:$L$6,MATCH($P$2,$E$1:$E$6,FALSE),8),INDEX($A$1:$L$6,MATCH($P$2,$I$1:$I$6,FALSE),12)))

Where P2 is the cell I entered the Value into, with a test value of 2075 I get the following:
Daily Rate: 8.11
Weekly Rate: 40.55
Monthly Rate: 11.39
 
Upvote 0
Here is a VBA option even though you didn't want one. It may be helpful in the future for others to understand. BTW--what do you have against vba solutions if they make life easier and cleaner.

Code: 
Option Explicit


Sub CurrencyFind()
    Dim c As Range, lr As Long
    Dim rng As Range, d As Currency, w As Currency, m As Currency
    Dim crit As Currency
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Set rng = Range("A2:I" & lr)
    crit = InputBox("What Value to search?")
    For Each c In rng
        If c = crit Then
            d = c.Offset(, 1)
            w = c.Offset(, 2)
            m = c.Offset(, 3)
        End If
    Next c
    MsgBox ("Rates are " & d & ", " & w & ", " & m)
End Sub
 
Last edited:
Upvote 0
Assuming your Data starts in cell A1 with only 4 columns and your input cell being B25 (Change the range from 15 and the input cell to whatever applies to your sheet)

Daily Rate : =IFERROR(INDEX($B$2:$B$15,AGGREGATE(15,6,((ROW($A$2:$A$15)-ROW($A$2)+1))/(($A$2:$A$15=$B$25)),ROWS($A$1:A1))),"")
Weekly Rate : =IFERROR(INDEX($C$2:$C$15,AGGREGATE(15,6,((ROW($A$2:$A$15)-ROW($A$2)+1))/(($A$2:$A$15=$B$25)),ROWS($A$1:A1))),"")
Monthly Rate : =IFERROR(INDEX($D$2:$D$15,AGGREGATE(15,6,((ROW($A$2:$A$15)-ROW($A$2)+1))/(($A$2:$A$15=$B$25)),ROWS($A$1:A1))),"")
 
Last edited:
Upvote 0
Try this array formulas

<b>Hoja3</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:89.35px;" /><col style="width:66.53px;" /><col style="width:81.74px;" /><col style="width:89.35px;" /><col style="width:60.83px;" /><col style="width:66.53px;" /><col style="width:81.74px;" /><col style="width:86.5px;" /><col style="width:60.83px;" /><col style="width:66.53px;" /><col style="width:81.74px;" /><col style="width:86.5px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; ">Value</td><td style="background-color:#92d050; ">Daily Rate</td><td style="background-color:#92d050; ">Weekly Rate</td><td style="background-color:#92d050; ">Monthly Rate </td><td style="background-color:#92d050; ">Value</td><td style="background-color:#92d050; ">Daily Rate</td><td style="background-color:#92d050; ">Weekly Rate</td><td style="background-color:#92d050; ">Monthly Rate</td><td style="background-color:#92d050; ">Value</td><td style="background-color:#92d050; ">Daily Rate</td><td style="background-color:#92d050; ">Weekly Rate</td><td style="background-color:#92d050; ">Monthly Rate</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">$3,050.00</td><td style="text-align:right; ">$4.27</td><td style="text-align:right; ">$71.36</td><td style="text-align:right; ">$45.43</td><td style="text-align:right; ">$7,202.00</td><td style="text-align:right; ">$6.60</td><td style="text-align:right; ">$83.02</td><td style="text-align:right; ">$96.14</td><td style="text-align:right; ">$1,355.00</td><td style="text-align:right; ">$8.94</td><td style="text-align:right; ">$94.68</td><td style="text-align:right; ">$46.86</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">$5,506.00</td><td style="text-align:right; ">$0.04</td><td style="text-align:right; ">$50.18</td><td style="text-align:right; ">$53.29</td><td style="text-align:right; ">$9,281.00</td><td style="text-align:right; ">$2.16</td><td style="text-align:right; ">$60.78</td><td style="text-align:right; ">$99.40</td><td style="text-align:right; ">$3,056.00</td><td style="text-align:right; ">$4.28</td><td style="text-align:right; ">$71.38</td><td style="text-align:right; ">$45.50</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">$8,642.00</td><td style="text-align:right; ">$6.18</td><td style="text-align:right; ">$30.91</td><td style="text-align:right; ">$69.46</td><td style="background-color:#ffff00; text-align:right; ">$2,075.00</td><td style="background-color:#b8cce4; text-align:right; ">$8.11</td><td style="background-color:#b8cce4; text-align:right; ">$40.55</td><td style="background-color:#b8cce4; text-align:right; ">$11.39</td><td style="text-align:right; ">$5,507.00</td><td style="text-align:right; ">$0.04</td><td style="text-align:right; ">$50.18</td><td style="text-align:right; ">$53.30</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">$2,374.00</td><td style="text-align:right; ">$2.66</td><td style="text-align:right; ">$13.31</td><td style="text-align:right; ">$92.90</td><td style="text-align:right; ">$5,493.00</td><td style="text-align:right; ">$4.41</td><td style="text-align:right; ">$22.07</td><td style="text-align:right; ">$31.00</td><td style="text-align:right; ">$8,612.00</td><td style="text-align:right; ">$6.17</td><td style="text-align:right; ">$30.83</td><td style="text-align:right; ">$69.09</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">$6,706.00</td><td style="text-align:right; ">$9.63</td><td style="text-align:right; ">$8.15</td><td style="text-align:right; ">$26.93</td><td style="text-align:right; ">$9,541.00</td><td style="text-align:right; ">$1.07</td><td style="text-align:right; ">$5.36</td><td style="text-align:right; ">$58.30</td><td style="text-align:right; ">$2,376.00</td><td style="text-align:right; ">$2.66</td><td style="text-align:right; ">$13.32</td><td style="text-align:right; ">$92.93</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Value:</td><td style="background-color:#ffff00; text-align:right; ">$2,075.00</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Daily Rate:</td><td style="background-color:#b8cce4; text-align:right; ">$8.11</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Weekly Rate:</td><td style="background-color:#b8cce4; text-align:right; ">$40.55</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >Monthly Rate:</td><td style="background-color:#b8cce4; text-align:right; ">$11.39</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formulas</td></tr><tr><td >B10</td><td >{=MAX(($A$2:$I$6=$B$9)*B$2:J$6)}</td></tr><tr><td >B11</td><td >{=MAX(($A$2:$I$6=$B$9)*C$2:K$6)}</td></tr><tr><td >B12</td><td >{=MAX(($A$2:$I$6=$B$9)*D$2:L$6)}</td></tr></table></td></tr></table>
 
Upvote 0
the same but with "regular" formula

=SUMPRODUCT((A2:I6=B9)*(B2:J6))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Search table for matching value and return another value
Replies
0
Views
528
Sirod
S

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,617
Latest member
Narendra Babu D

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