What is the most efficient way to solve this both the problems and are there multiple ways to solve them? Please let me know. Much appreciated!

[robin_121]

I have tried solving the problem Q1 and Q2 and I am getting errors. I have attached the images with this. PS- Q1 and Q2 are the questions. Tab1, Tab2 and Tab3 is the data I have to work on. How do I got about the same? Please help me out. Thanks!

 

[Michael M]

Looks like an assignment to me.....You'll find most posters don't assist with homework....as we don't get the credit for doing it !!

 

[jasonb75]

What guidance have you been given on how to solve it? There are many ways do solve the first one, but your course tutor may expect you to use specific methods.
Additionally, some methods will be specific to certain versions of excel (I'm sure that you're not using 6 versions at once so it would be a good idea to update your profile so that it only shows the correct one).

The second question specifically asks for a pivot table so I can't see how you would get errors from doing that, what have you tried?

Remember, errors and incorrect results are 2 entirely different things.

 

[robin_121]

What guidance have you been given on how to solve it? There are many ways do solve the first one, but your course tutor may expect you to use specific methods.
Additionally, some methods will be specific to certain versions of excel (I'm sure that you're not using 6 versions at once so it would be a good idea to update your profile so that it only shows the correct one).

The second question specifically asks for a pivot table so I can't see how you would get errors from doing that, what have you tried?

Remember, errors and incorrect results are 2 entirely different things.

Hi Jason,

This is not my homework. I am a working professional and I just received this excel sheet from one of my friends who gave an excel test at a company. So, I was just trying to solve this problem for my practice. For Q1, I have used VLOOKUP(D2, A$2:B$451, 2, 0). I was able to match the key with the values in both tabs. But I want to select only the the first value corresponding to the common key and I was unable to do it.

 

[robin_121]

Looks like an assignment to me.....You'll find most posters don't assist with homework....as we don't get the credit for doing it !!

This is not my homework. I am a working professional and I just received this excel sheet from one of my friends who gave an excel test at a company. So, I was just trying to solve this problem for my practice. For Q1, I have used VLOOKUP(D2, A$2:B$451, 2, 0). I was able to match the key with the values in both tabs. But I want to select only the the first value corresponding to the common key and I was unable to do it.

 

[jasonb75]

For Q1, this is how I would do it. I've used the question sheet layout to demonstrate but the same principle can be applied to 2 sheets as easily as 2 ranges.

You may not be able to use this method though. As I said previously, many methods will be specific to certain versions of excel and it is not clear which one you are using.

Book1
	A	B	C
1	Sheet1
2	Key
3	1	10
4	2	20
5	3	30
6	1	40
7
8
9	Sheet2
10	Key
11	1	100
12	3	300
13	4	400
14
15
16
17	Result
18	Key	Tab1	Tab2
19	1	10	100
20	3	30	300
Sheet1

Cell Formulas
Range	Formula
A19:A20	A19	=UNIQUE(FILTER(A3:A6,ISNUMBER(MATCH(A3:A6,A11:A13,0)),""))
B19:B20	B19	=VLOOKUP(A19#,A3:B6,2,0)
C19:C20	C19	=VLOOKUP(A19#,A11:B13,2,0)
Dynamic array formulas.

 

[robin_121]

For Q1, this is how I would do it. I've used the question sheet layout to demonstrate but the same principle can be applied to 2 sheets as easily as 2 ranges.

You may not be able to use this method though. As I said previously, many methods will be specific to certain versions of excel and it is not clear which one you are using.

Book1
	A	B	C
1	Sheet1
2	Key
3	1	10
4	2	20
5	3	30
6	1	40
7
8
9	Sheet2
10	Key
11	1	100
12	3	300
13	4	400
14
15
16
17	Result
18	Key	Tab1	Tab2
19	1	10	100
20	3	30	300
Sheet1

Cell Formulas
Range	Formula
A19:A20	A19	=UNIQUE(FILTER(A3:A6,ISNUMBER(MATCH(A3:A6,A11:A13,0)),""))
B19:B20	B19	=VLOOKUP(A19#,A3:B6,2,0)
C19:C20	C19	=VLOOKUP(A19#,A11:B13,2,0)
Dynamic array formulas.

thanks a lot!