Returning a value from a table based on two criteria

[povictory]

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.

	A	B	C
1	Employee1	Unit1	Value from table that matches criteria in column A and B
2	Employee1	Unit2
3	Employee1	Unit3
4	Employee1	Unit4
5	Employee2	Unit10
6	Employee2	Unit11
7	Employee3	Unit5
8	Employee3	Unit6
9	Employee3	Unit7
10	Employee3	Unit8
11	Employee3	Unit9
12	Employee3	Unit10

<tbody>
</tbody>

Lookup table:

	A	B	C	D	E	F	G	H	I	J	K
1		Unit1	Unit2	Unit3	Unit4	Unit5	Unit6	Unit7	Unit8	Unit9	Unit10
2	Employee1	5,000	10,000	5,000	10,000
3	Employee2										5,000
4	Employee3					5,000	5,000	10,000	15,000	9,000	4,000
5	Employee4							6,000
6	Employee5			3,000
7	Employee6					7,000
8	Employee7
9	Employee8										17,000
10	Employee9		4,000								5,000
11	Employee10					15,000
12	Employee11								2,000

<tbody>
</tbody>

Any help is GREATLY appreciated!!

Thanks!

 

[Peter_SSs]

Try adapting this, copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1						Unit1	Unit2	Unit3	Unit4	Unit5	Unit6	Unit7	Unit8	Unit9	Unit10
2	Employee1	Unit1	5000		Employee1	5,000	10,000	5,000	10,000
3	Employee1	Unit2	10000		Employee2										5,000
4	Employee1	Unit3	5000		Employee3					5,000	5,000	10,000	15,000	9,000	4,000
5	Employee1	Unit4	10000		Employee4							6,000
6	Employee2	Unit10	5000		Employee5			3,000
7	Employee2	Unit11	#N/A		Employee6					7,000
8	Employee3	Unit5	5000		Employee7
9	Employee3	Unit6	5000		Employee8										17,000
10	Employee3	Unit7	10000		Employee9		4,000								5,000
11	Employee3	Unit8	15000		Employee10					15,000
12	Employee3	Unit9	9000		Employee11								2,000
13	Employee3	Unit10	4000
Lookup Table

 

[initialize]

Try adapting this, copied down.

Lookup Table

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

*

*

*

*

*

Unit1

Unit2

Unit3

Unit4

Unit5

Unit6

Unit7

Unit8

Unit9

Unit10

2

Employee1

Unit1

5000

*

Employee1

5,000

10,000

5,000

10,000

*

*

*

*

*

*

3

Employee1

Unit2

10000

*

Employee2

*

*

*

*

*

*

*

*

*

5,000

4

Employee1

Unit3

5000

*

Employee3

*

*

*

*

5,000

5,000

10,000

15,000

9,000

4,000

5

Employee1

Unit4

10000

*

Employee4

*

*

*

*

*

*

6,000

*

*

*

6

Employee2

Unit10

5000

*

Employee5

*

*

3,000

*

*

*

*

*

*

*

7

Employee2

Unit11

#N/A

*

Employee6

*

*

*

*

7,000

*

*

*

*

*

8

Employee3

Unit5

5000

*

Employee7

*

*

*

*

*

*

*

*

*

*

9

Employee3

Unit6

5000

*

Employee8

*

*

*

*

*

*

*

*

*

17,000

10

Employee3

Unit7

10000

*

Employee9

*

4,000

*

*

*

*

*

*

*

5,000

11

Employee3

Unit8

15000

*

Employee10

*

*

*

*

15,000

*

*

*

*

*

12

Employee3

Unit9

9000

*

Employee11

*

*

*

*

*

*

*

2,000

*

*

13

Employee3

Unit10

4000

*

*

*

*

*

*

*

*

*

*

*

*

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

Hi Peter,

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

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!

 

[povictory]

This is exactly what I was looking for...thank you so much!!

 

[Peter_SSs]

Hi Peter,

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

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.

 

[Peter_SSs]

This is exactly what I was looking for...thank you so much!!

Glad you had a successful result. Thanks for letting us know.

 

[initialize]

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