How can i use lookup for data in reverse column ?
in [Functions]
|
From: Sandy Mann on 5 Jul 2008 07:08 Ramesh Jaiswal (9867936636)" <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... > The Formula provided by you is of Index. I required the Vlookup formula. Why? Is it Homework? -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2(a)mailinator.com Replace @mailinator.com with @tiscali.co.uk "Ramesh Jaiswal (9867936636)" <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... > Dear Sandy, > > The Formula provided by you is of Index. I required the Vlookup formula. > As describe i require the lookup in revers i.e. if any data form sheet 2 > is > present in second coloum in sheet 1, then i required the 1st data of the > respective field form sheet 1 to be appear in the column 2 of sheet 2. > > > =Vlookup(sheet1!$A$1:$B$8,-1,0) > > Ramesh Jaiswal > 9867936636 > > > "Sandy Mann" wrote: > >> Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 >> will >> do it thus: >> >> =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2))) >> >> -- >> HTH >> >> Sandy >> In Perth, the ancient capital of Scotland >> and the crowning place of kings >> >> sandymann2(a)mailinator.com >> Replace @mailinator.com with @tiscali.co.uk >> >> >> "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal >> (9867936636)@discussions.microsoft.com> wrote in message >> news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com... >> > How can Vlook work to provide the data in revers for example: >> > in one excel sheet my data is, >> > A B >> > 12 Raj >> > 55 Abhay >> > 96 Vinod >> > 75 Ganesh >> > >> > in another Excel sheet my data is, >> > A B >> > Abhay >> > Vinod >> > Raj >> > Ganesh >> > >> > I want the A column to be lookup and the value of the first sheet >> > column A >> > should appear. Is it possible. I know it is possible if i cust the B >> > column & past it in the A column and its work. But i do not want to >> > cut >> > the >> > same. >> > I have tried by no use. Please help me. >> > >> > Ramesh Jaiswal >> > 9867936636 >> > >> >> >> >
From: Ramesh Jaiswal (9867936636) on 5 Jul 2008 08:03 Dear sandy, This is not an Homework. I just want to take the column from left. Please confirm the same. Please provide the test details with Example is possible. Ramesh Jaiswal 9867936636 "Sandy Mann" wrote: > Ramesh Jaiswal (9867936636)" > <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message > news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... > > > The Formula provided by you is of Index. I required the Vlookup formula. > > Why? Is it Homework? > > -- > Regards, > > Sandy > In Perth, the ancient capital of Scotland > and the crowning place of kings > > sandymann2(a)mailinator.com > Replace @mailinator.com with @tiscali.co.uk > > > "Ramesh Jaiswal (9867936636)" > <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message > news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... > > Dear Sandy, > > > > The Formula provided by you is of Index. I required the Vlookup formula. > > As describe i require the lookup in revers i.e. if any data form sheet 2 > > is > > present in second coloum in sheet 1, then i required the 1st data of the > > respective field form sheet 1 to be appear in the column 2 of sheet 2. > > > > > > =Vlookup(sheet1!$A$1:$B$8,-1,0) > > > > Ramesh Jaiswal > > 9867936636 > > > > > > "Sandy Mann" wrote: > > > >> Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 > >> will > >> do it thus: > >> > >> =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2))) > >> > >> -- > >> HTH > >> > >> Sandy > >> In Perth, the ancient capital of Scotland > >> and the crowning place of kings > >> > >> sandymann2(a)mailinator.com > >> Replace @mailinator.com with @tiscali.co.uk > >> > >> > >> "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal > >> (9867936636)@discussions.microsoft.com> wrote in message > >> news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com... > >> > How can Vlook work to provide the data in revers for example: > >> > in one excel sheet my data is, > >> > A B > >> > 12 Raj > >> > 55 Abhay > >> > 96 Vinod > >> > 75 Ganesh > >> > > >> > in another Excel sheet my data is, > >> > A B > >> > Abhay > >> > Vinod > >> > Raj > >> > Ganesh > >> > > >> > I want the A column to be lookup and the value of the first sheet > >> > column A > >> > should appear. Is it possible. I know it is possible if i cust the B > >> > column & past it in the A column and its work. But i do not want to > >> > cut > >> > the > >> > same. > >> > I have tried by no use. Please help me. > >> > > >> > Ramesh Jaiswal > >> > 9867936636 > >> > > >> > >> > >> > > > > >
From: Ramesh Jaiswal (9867936636) on 5 Jul 2008 08:07 Dear Mike, Any other solution. Please confirm..... If possible plesae provde the alternet solution with example in formula..... It will help me a lot. Ramesh Jaiswal 9867936636 "Mike H" wrote: > It doesn't work, you cannot Vlookup and return a column to the left > > "Ramesh Jaiswal (9867936636)" wrote: > > > Dear Sandy, > > > > The Formula provided by you is of Index. I required the Vlookup formula. > > As describe i require the lookup in revers i.e. if any data form sheet 2 is > > present in second coloum in sheet 1, then i required the 1st data of the > > respective field form sheet 1 to be appear in the column 2 of sheet 2. > > > > > > =Vlookup(sheet1!$A$1:$B$8,-1,0) > > > > Ramesh Jaiswal > > 9867936636 > > > > > > "Sandy Mann" wrote: > > > > > Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 will > > > do it thus: > > > > > > =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2))) > > > > > > -- > > > HTH > > > > > > Sandy > > > In Perth, the ancient capital of Scotland > > > and the crowning place of kings > > > > > > sandymann2(a)mailinator.com > > > Replace @mailinator.com with @tiscali.co.uk > > > > > > > > > "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal > > > (9867936636)@discussions.microsoft.com> wrote in message > > > news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com... > > > > How can Vlook work to provide the data in revers for example: > > > > in one excel sheet my data is, > > > > A B > > > > 12 Raj > > > > 55 Abhay > > > > 96 Vinod > > > > 75 Ganesh > > > > > > > > in another Excel sheet my data is, > > > > A B > > > > Abhay > > > > Vinod > > > > Raj > > > > Ganesh > > > > > > > > I want the A column to be lookup and the value of the first sheet column A > > > > should appear. Is it possible. I know it is possible if i cust the B > > > > column & past it in the A column and its work. But i do not want to cut > > > > the > > > > same. > > > > I have tried by no use. Please help me. > > > > > > > > Ramesh Jaiswal > > > > 9867936636 > > > > > > > > > > > > >
From: Sandy Mann on 5 Jul 2008 08:17 I was completely misreading your post. As Mike H says, VLOOKUP() simply does not work that way. You have to use something like INDEX() & MATCH() as Mike does in his formula, adjusting the Sheet/Range as required. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2(a)mailinator.com Replace @mailinator.com with @tiscali.co.uk "Ramesh Jaiswal (9867936636)" <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message news:4AB810DF-B9D6-4D7E-B2CE-115667402E8B(a)microsoft.com... > Dear sandy, > > This is not an Homework. I just want to take the column from left. > Please > confirm the same. Please provide the test details with Example is > possible. > > Ramesh Jaiswal > 9867936636 > > "Sandy Mann" wrote: > >> Ramesh Jaiswal (9867936636)" >> <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message >> news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... >> >> > The Formula provided by you is of Index. I required the Vlookup >> > formula. >> >> Why? Is it Homework? >> >> -- >> Regards, >> >> Sandy >> In Perth, the ancient capital of Scotland >> and the crowning place of kings >> >> sandymann2(a)mailinator.com >> Replace @mailinator.com with @tiscali.co.uk >> >> >> "Ramesh Jaiswal (9867936636)" >> <RameshJaiswal9867936636(a)discussions.microsoft.com> wrote in message >> news:50B227AD-5409-4ED8-84EB-493C3E6156B8(a)microsoft.com... >> > Dear Sandy, >> > >> > The Formula provided by you is of Index. I required the Vlookup >> > formula. >> > As describe i require the lookup in revers i.e. if any data form sheet >> > 2 >> > is >> > present in second coloum in sheet 1, then i required the 1st data of >> > the >> > respective field form sheet 1 to be appear in the column 2 of sheet 2. >> > >> > >> > =Vlookup(sheet1!$A$1:$B$8,-1,0) >> > >> > Ramesh Jaiswal >> > 9867936636 >> > >> > >> > "Sandy Mann" wrote: >> > >> >> Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in >> >> A2:A5 >> >> will >> >> do it thus: >> >> >> >> =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2))) >> >> >> >> -- >> >> HTH >> >> >> >> Sandy >> >> In Perth, the ancient capital of Scotland >> >> and the crowning place of kings >> >> >> >> sandymann2(a)mailinator.com >> >> Replace @mailinator.com with @tiscali.co.uk >> >> >> >> >> >> "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal >> >> (9867936636)@discussions.microsoft.com> wrote in message >> >> news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com... >> >> > How can Vlook work to provide the data in revers for example: >> >> > in one excel sheet my data is, >> >> > A B >> >> > 12 Raj >> >> > 55 Abhay >> >> > 96 Vinod >> >> > 75 Ganesh >> >> > >> >> > in another Excel sheet my data is, >> >> > A B >> >> > Abhay >> >> > Vinod >> >> > Raj >> >> > Ganesh >> >> > >> >> > I want the A column to be lookup and the value of the first sheet >> >> > column A >> >> > should appear. Is it possible. I know it is possible if i cust >> >> > the B >> >> > column & past it in the A column and its work. But i do not want to >> >> > cut >> >> > the >> >> > same. >> >> > I have tried by no use. Please help me. >> >> > >> >> > Ramesh Jaiswal >> >> > 9867936636 >> >> > >> >> >> >> >> >> >> > >> >> >> >
From: Mike H on 5 Jul 2008 08:39 Hi, The solution I gave you in my first post does exactly what you require. All you will have to do is change the ranges to suit your needs. Mike "Ramesh Jaiswal (9867936636)" wrote: > > Dear Mike, > > Any other solution. Please confirm..... If possible plesae provde the > alternet solution with example in formula..... It will help me a lot. > > Ramesh Jaiswal > 9867936636 > > "Mike H" wrote: > > > It doesn't work, you cannot Vlookup and return a column to the left > > > > "Ramesh Jaiswal (9867936636)" wrote: > > > > > Dear Sandy, > > > > > > The Formula provided by you is of Index. I required the Vlookup formula. > > > As describe i require the lookup in revers i.e. if any data form sheet 2 is > > > present in second coloum in sheet 1, then i required the 1st data of the > > > respective field form sheet 1 to be appear in the column 2 of sheet 2. > > > > > > > > > =Vlookup(sheet1!$A$1:$B$8,-1,0) > > > > > > Ramesh Jaiswal > > > 9867936636 > > > > > > > > > "Sandy Mann" wrote: > > > > > > > Does it have to be VLOOKUP()? INDEX() with the data in Sheet1 in A2:A5 will > > > > do it thus: > > > > > > > > =INDEX(Sheet1!$B$2:$B$5,COUNTA(Sheet1!$B$2:$B$5)-(ROW()-ROW($A$2))) > > > > > > > > -- > > > > HTH > > > > > > > > Sandy > > > > In Perth, the ancient capital of Scotland > > > > and the crowning place of kings > > > > > > > > sandymann2(a)mailinator.com > > > > Replace @mailinator.com with @tiscali.co.uk > > > > > > > > > > > > "Ramesh Jaiswal (9867936636)" <Ramesh Jaiswal > > > > (9867936636)@discussions.microsoft.com> wrote in message > > > > news:5574D832-10DD-4162-B9CB-ABEBADF98C6D(a)microsoft.com... > > > > > How can Vlook work to provide the data in revers for example: > > > > > in one excel sheet my data is, > > > > > A B > > > > > 12 Raj > > > > > 55 Abhay > > > > > 96 Vinod > > > > > 75 Ganesh > > > > > > > > > > in another Excel sheet my data is, > > > > > A B > > > > > Abhay > > > > > Vinod > > > > > Raj > > > > > Ganesh > > > > > > > > > > I want the A column to be lookup and the value of the first sheet column A > > > > > should appear. Is it possible. I know it is possible if i cust the B > > > > > column & past it in the A column and its work. But i do not want to cut > > > > > the > > > > > same. > > > > > I have tried by no use. Please help me. > > > > > > > > > > Ramesh Jaiswal > > > > > 9867936636 > > > > > > > > > > > > > > > > >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Counting incidences of surname and date of birth and rearrangi Next: Searching from a table |