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

New Member
Joined
Feb 5, 2021
Messages
4
Office Version
  1. 2019
  2. 2016
  3. 2013
  4. 2011
  5. 2010
  6. 2007
Platform
  1. Windows
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!
 

Attachments

  • Q1.png
    Q1.png
    54.9 KB · Views: 25
  • Q2.png
    Q2.png
    65.7 KB · Views: 24
  • Tab1.png
    Tab1.png
    57.6 KB · Views: 24
  • Tab2.png
    Tab2.png
    56.5 KB · Views: 21
  • Tab3.png
    Tab3.png
    90.9 KB · Views: 24

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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 !!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
ABC
1Sheet1
2Key
3110
4220
5330
6140
7
8
9Sheet2
10Key
111100
123300
134400
14
15
16
17Result
18KeyTab1Tab2
19110100
20330300
Sheet1
Cell Formulas
RangeFormula
A19:A20A19=UNIQUE(FILTER(A3:A6,ISNUMBER(MATCH(A3:A6,A11:A13,0)),""))
B19:B20B19=VLOOKUP(A19#,A3:B6,2,0)
C19:C20C19=VLOOKUP(A19#,A11:B13,2,0)
Dynamic array formulas.
 
Upvote 0
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
ABC
1Sheet1
2Key
3110
4220
5330
6140
7
8
9Sheet2
10Key
111100
123300
134400
14
15
16
17Result
18KeyTab1Tab2
19110100
20330300
Sheet1
Cell Formulas
RangeFormula
A19:A20A19=UNIQUE(FILTER(A3:A6,ISNUMBER(MATCH(A3:A6,A11:A13,0)),""))
B19:B20B19=VLOOKUP(A19#,A3:B6,2,0)
C19:C20C19=VLOOKUP(A19#,A11:B13,2,0)
Dynamic array formulas.
thanks a lot!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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