Returning a value from a table based on two criteria

P

povictory

New Member
Joined
May 28, 2015
Messages
45
Hello,

I'm looking for help on a formula to retrieve a value from a table based on two criteria. I'm sure there's probably a fairly easy way to do it, but I'm just struggling to find the best way to do it myself so I'm hoping someone here might be able to help.

I have a list of employees in column A and a list of units in column B. Both employees and units can be repeated in the columns, however each combination of the two is unique.

I also have a look-up table with the employee names listed in the rows and the units listed in the columns. What I'm trying to do is create a formula that will pull a value in the lookup table that matches the employee/unit combo in columns A and B.


[TABLE="width: 271"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee1[/TD]
[TD]Unit1[/TD]
[TD]Value from table that matches criteria in column A and B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee1[/TD]
[TD]Unit2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Employee1[/TD]
[TD]Unit3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee1[/TD]
[TD]Unit4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Employee2[/TD]
[TD]Unit10[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Employee2[/TD]
[TD]Unit11[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Employee3[/TD]
[TD]Unit5[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Employee3[/TD]
[TD]Unit6[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Employee3[/TD]
[TD]Unit7[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Employee3[/TD]
[TD]Unit8[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Employee3[/TD]
[TD]Unit9[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Employee3[/TD]
[TD]Unit10[/TD]
[/TR]
</tbody>[/TABLE]

Lookup table:
[TABLE="width: 790"]
<tbody>[TR]
[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]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Unit1[/TD]
[TD]Unit2[/TD]
[TD]Unit3[/TD]
[TD]Unit4[/TD]
[TD]Unit5[/TD]
[TD]Unit6[/TD]
[TD]Unit7[/TD]
[TD]Unit8[/TD]
[TD]Unit9[/TD]
[TD]Unit10[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Employee1[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Employee2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Employee3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]15,000[/TD]
[TD="align: right"]9,000[/TD]
[TD="align: right"]4,000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Employee4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Employee5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Employee6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Employee7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Employee8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17,000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Employee9[/TD]
[TD][/TD]
[TD="align: right"]4,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5,000[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Employee10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Employee11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is GREATLY appreciated!!

Thanks!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Try adapting this, copied down.

Excel Workbook
ABCDEFGHIJKLMNO
1Unit1Unit2Unit3Unit4Unit5Unit6Unit7Unit8Unit9Unit10
2Employee1Unit15000Employee15,00010,0005,00010,000
3Employee1Unit210000Employee25,000
4Employee1Unit35000Employee35,0005,00010,00015,0009,0004,000
5Employee1Unit410000Employee46,000
6Employee2Unit105000Employee53,000
7Employee2Unit11#N/AEmployee67,000
8Employee3Unit55000Employee7
9Employee3Unit65000Employee817,000
10Employee3Unit710000Employee94,0005,000
11Employee3Unit815000Employee1015,000
12Employee3Unit99000Employee112,000
13Employee3Unit104000
Lookup Table
 
Upvote 0
Peter_SSs said:
Try adapting this, copied down.

Lookup Table

*ABCDEFGHIJKLMNO
*****
Employee1Unit1*Employee1******
Employee1Unit2*Employee2*********
Employee1Unit3*Employee3****
Employee1Unit4*Employee4*********
Employee2Unit10*Employee5*********
Employee2Unit11#N/A*Employee6*********
Employee3Unit5*Employee7**********
Employee3Unit6*Employee8*********
Employee3Unit7*Employee9********
Employee3Unit8*Employee10*********
Employee3Unit9*Employee11*********
Employee3Unit10************

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:84px;"><col style="width:56px;"><col style="width:66px;"><col style="width:53px;"><col style="width:92px;"><col style="width:51px;"><col style="width:58px;"><col style="width:51px;"><col style="width:58px;"><col style="width:58px;"><col style="width:51px;"><col style="width:58px;"><col style="width:58px;"><col style="width:51px;"><col style="width:58px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]Unit1[/TD]
[TD="align: right"]Unit2[/TD]
[TD="align: right"]Unit3[/TD]
[TD="align: right"]Unit4[/TD]
[TD="align: right"]Unit5[/TD]
[TD="align: right"]Unit6[/TD]
[TD="align: right"]Unit7[/TD]
[TD="align: right"]Unit8[/TD]
[TD="align: right"]Unit9[/TD]
[TD="align: right"]Unit10[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]5000[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]10000[/TD]

[TD="align: right"]5,000[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]5000[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]5,000[/TD]
[TD="align: right"]10,000[/TD]
[TD="align: right"]15,000[/TD]
[TD="align: right"]9,000[/TD]
[TD="align: right"]4,000[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]10000[/TD]

[TD="align: right"]6,000[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]5000[/TD]

[TD="align: right"]3,000[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]7,000[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]5000[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]5000[/TD]

[TD="align: right"]17,000[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]10000[/TD]

[TD="align: right"]4,000[/TD]

[TD="align: right"]5,000[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]15000[/TD]

[TD="align: right"]15,000[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]9000[/TD]

[TD="align: right"]2,000[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]4000[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
C2=INDEX(F$2:O$12,MATCH(A2,E$2:E$12,0),MATCH(B2,F$1:O$1,0))

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...



Hi Peter,

I'm fairly new to excel and vba. And I'm wondering what if I have done this using vba?
for example:

Code: 
Sub findthem()


    Dim i As Integer
    Dim employN, employU As String
    Dim rowT, colT As Long
    
    For i = 2 To 13
        employN = Range("A" & i).Value
        employU = Range("B" & i).Value
        rowT = Columns(5).Find(what:=employN).Row
        colT = Rows(1).Find(what:=employU).Column
        
        Range("C" & i).Value = Cells(rowT, colT).Value
        
    Next i


End Sub


Assume the person has a large chart, will it be significantly slower if I were to do it in VBA instead?
Also do you have any books/websites recommendation in learning excel and/or vba? I am from a computer science background trying to get into a bank, and I've seen a lot of jobs requiring vba/excel. Hope you can give me advice. Thanks!
 
Upvote 0
initialize said:
Hi Peter,

I'm fairly new to excel and vba. And I'm wondering what if I have done this using vba?
Click to expand...
Well, you can pretty easily test your own code - and perhaps have already done so.
I haven't tested your code but do have some comments.
1. vba could be very fast. There would be faster ways than using Find (twice) but if the data is not too big that probably wouldn't matter & in any case you are getting practice with that method.
2. You may find some issues with your 'Find' lines. vba Find has quite a few parameters and you need to be careful about leaving some of them out as you have done as Excel remembers some of those settings from a previous Find (vba or manual). For example if the previous Find was looking for a partial match, then when you do a Find for "Unit1" in row 1 you would want to ensure it didn't do a partial match and find "Unit10" instead.
3. When declaring variables, if you want to specify the type (good practice) you need to specify each individual variable. For example ..

Dim rowT, colT As Long

.. this is only declaring colT as Long and rowT will be a Variant because you haven't specified. You would need ..

Dim rowT as Long, colT As Long

4. Also on variable declarations, my understanding is that vba converts all Integer types to Long before using them, so you might as well just declare them as Long to start with. Besides, it is shorter to type. ;)
5. I have done virtually all of my vba learning on this site - doing just what you have done. Find a problem, have a go, learn from feedback, keep going. :)
 
Upvote 0
Thank you for pointing out those concerns, I really had no idea about the find function and haha I assumed dim rowT, rowT as long would declare both as long.

Yeah I figure this site is a good idea, since everyone is posting their real life problem. I've already learnt quite a few from you and a few other posts!

Thanks again, you guys are awesome
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
SUMIF Multiple Columns based on criteria next to each cell
Replies
3
Views
545
Fluff
Fluff
N
  • Solved
Problem populating table based off two criteria from a different table
Replies
1
Views
865
Nade85
N
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
781
Viau1337
V
M
  • Solved
Stop a search/filter formula based on two columns from duplicating a row that has a hit in both columns
Replies
1
Views
799
Marzatron
M
T
  • Question Question
Sumproduct not correct
2
Replies
13
Views
774
Tigerexcel
T

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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