Display records on different sheets using vlookup

[justmine584]

Hi,
I have a problem that i want to display searched record on different sheet. Below are the details.

-I have 2 sheets named sheet1 & sheet2
-Sheet1 contain records of students like Age,Name,marks etc

Age Name Marks
10 A 60
11 B 60
11 C 30
9 D 50
10 E 40

I want to display records search condition on Age. Means on sheet2 if user enter any age in A1 cell then search data from sheet1 should be displayed on sheet2 cells (starting from D4 to ...). Record should be displayed at any location where formula will set by user. Column titles also different on both sheets.



Please help me guys. I need urgent solution.

 

[justmine584]

Nothing wrong with that file. You did that with Pivot Table. But requirement is that task should be done with Vlookup. Is VBA helpful for this?

 

[Akashwani]

Hi,

Well, I'm please that the file is Ok, but the vlookup (Hlookup, Lookup) issue is still the same, as far as I'm aware you cannot do it. On Sheet2 of the last file I posted, in cells B5:C5 I have copied the formula down to say 120 rows, this is the only way that I can think of for you to get the data you require using a formula.

I added a Pivot Table to Sheet2 to show you what a Pivot Table can do, if you use the filter at the top of the Pivot Table that will do exactly the same as the formula in B5:C120, but it doesn't take as long to calculate etc, also you can then create a Chart from the Pivot Table data if you wish or rearrange the data to give you different summary options.

I've made a few changes to Sheet2 to show you some more Pivot Table options, I strongly believe that the Pivot Table is the better option for you, but the formulas I have given you on Sheet2 also do as you require.

justmine584.xlsx


Hopefully someone else will read this thread and look at your file or mine and tell me that I'm totally wrong in my assessment of the capabilities of Vlookup or confirm that my solution(s) are the only way for you to get the desired result that you seek.

Good luck

Ak

 

[justmine584]

You did good work with pivot Table but requirement is that it should be done with vlookup. can it be done with vba? Is it possible.

 

[Akashwani]

Hi,

I don't write or understand VBA, so I cannot help you with that. I suggest that you start a new thread asking for a "VBA alternative to Vlookup", and explain what you want the VBA code to do, but Vlookup is the wrong term to be using.

One last question, ignore the Pivot Table, what is wrong with using the formulas provided? Is this school work?

Ak

 

[justmine584]

Nothing wrong with that formula. Working fine. Anyways thanx for your efforts and give me alternative solutions. Thanx for you precious time.

 

[nguyenthao_89]

Hi,

Welcome to MrExcel.

Is this the type of data you have on Sheet1 and the result you expect on Sheet2?...

Excel Workbook
	A	B	C
1	Age	Name	Mark
2	10	Bob	80
3	12	Tom	65
4	11	Jo	55
5	10	Joe	75
6	11	Jill	80
7	11	Bill	50
8	12	Tim	60
9	12	Jen	55
10	10	Kim	75
Sheet1

Excel Workbook
	A	B	C	D
1	*	Age	Name	Mark
2	3	10	Bob	80
3	*	*	Joe	75
4	*	*	Kim	75
Sheet2

You can change the sheet name and cell references to suit, but you MUST enter the formula in C2 with ctrll-shift-enter not just enter. You can then copy the formula across and down as far as you require.

Good luck

Ak

Hi,
I am sorry to open this again, but I have a question related to the above formula. My situation is more or less similar with it.
I have to get the name of different employees involed in a project. I have used the above formula. However, it just gives the correct result for the first project, and totally wrong for the rest.
Could you please take a look at my excel sheet and suggest me a solution for it? Thank you in advance.
http://www.megaupload.com/?d=BKZNDC5J

 

[Akashwani]

Hi nguyenthao_89

Have you used the correct cell references?
Have you entered the formula with ctrl-shift-enter.

Good luck.

Ak

 

[nguyenthao_89]

Hi nguyenthao_89

Have you used the correct cell references?
Have you entered the formula with ctrl-shift-enter.

Good luck.

Ak

Hi Ak,
I have used the correct cell references I believed! And I've entered formulas with ctrl-shift-enter as well. As i described above, the formulas do work for the first project, but it goes wrong for the rest. Could you please take a look at my file? Thanks alot alot alot

 

[Akashwani]

Hi,

If I understand correctly what you are trying to do, then I think that you need to change some of your cell references.
Take a look here......

MrExcel help reply.xlsx

I don't think that the solution you are trying to use is the best way to do what you want. You may want to post a new question outlining what data you have and which data you want to return from it based on your varying criteria.

Good luck.

Ak

 

[nguyenthao_89]

Hi,

If I understand correctly what you are trying to do, then I think that you need to change some of your cell references.
Take a look here......

MrExcel help reply.xlsx

I don't think that the solution you are trying to use is the best way to do what you want. You may want to post a new question outlining what data you have and which data you want to return from it based on your varying criteria.

Good luck.
Ak

Hi Ak,
Thanks for your suggestion. I think it's a better idea to post a new threat Btw, I have tried ur advice about cell reference with my sheet but still didn't work well.
Hope you can take a look at my new post, here:
http://www.mrexcel.com/forum/showthread.php?p=2842137#post2842137

Thanks alot for your time

Jenny