Need to use info from one tab of sheet to another(almost like Vlookup )

joyofgiving

New Member
Joined
Nov 19, 2014
Messages
2
Report with following data which has the Course ID & related info
Course IdStart DateEnd DateCourse TypeCityStateTeacher(s)
BC002211/15/1311/19/13Course oneVancouverBritish ColumbiaMR XYZ
BC00023/28/133/31/13Art of SilenceBritish ColumbiaMS OPQ
BC002111/21/1311/24/13Course super oneVancouverBritish ColumbiaMR ABC
BC002012/18/132/21/14Fantastic courseChilliwackBritish ColumbiaMR EFG
ALTA844/10/144/14/14Art of Living Happiness ProgramCalgaryAlbertaMS HIJ
ALTA914/24/144/27/14Art of LivingEdmontonAlbertaMS LMN
I WANT TO BE ABLE TO OBTAIN THE INFO FROM THE ABOVE REPORT FOR CITY ,STATE & TEACHERS AND LOAD INTO ANOTHER SHEET , SHOWN HEREUNDER ,WHICH HAS COURSE ID INFO INCLUDED SAME AS FIRST COLUMN ABOVE BUT WITH OTHER INFO AS WELL.
IS THERE ANY FORMULA OR WAY TO BE ABLE TO DO SOMETHING SIMILAR TO VLOOKUP AND GET THE CITY AND TEACHERS INFO FROM LEFT SIDE OF THE REPORT TO THE REPORT HEREUNDER ( THESE ARE TWO DIFF TABS IN ACTUAL WORKSHEET)
S|CRS|ALTA84|10 Apr 2014|ALTA|Art of Living Happi
S|CRS|BC0037|12 Jun 2014|BC|Art of Living Happine
S|CRS|QUE087|19 Jun 2014|QUE|Art of Living Happin
S|CRS|ALTA95|05 Jun 2014|ALTA|Art of Living Happi
Doing text to data and then Vlookup is very timeconsuming and complex as the column has multiple diff types of info so want a easier way. thanks!


<tbody>
</tbody>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
you could use INDEX/MATCH instead of Vlookup.

could you attach a sample file with expected output that would be useful
 
Last edited:
Upvote 0
Thank you AlanY

But I dont know how to use Index for getting the tab one info into the tab two hereunder. Is there any VBA script or formula i could use? or can you guide me how to use Index for getting city information of the tab one into tab two? I cant past in excel so doesnot come with formatting. I hope this helps? thanks

Tab one
Course IdStart DateEnd DateCourse TypeCityStateTeacher(s)
BC00222013-11-152013-11-19Course oneVancouverBritish ColumbiaMR XYZ
BC00022013-03-282013-03-31Art of Silence British ColumbiaMS OPQ
BC00212013-11-212013-11-24Course super oneVancouverBritish ColumbiaMR ABC
BC00202013-12-182014-02-21Fantastic courseChilliwackBritish ColumbiaMR EFG
ALTA842014-04-102014-04-14Art of Living Happiness ProgramCalgaryAlbertaMS HIJ
ALTA912014-04-242014-04-27Art of LivingEdmontonAlbertaMS LMN
Tab two
Date Amount Revenue SourceNote Deposit NumberAccount NumberCustomer IDAuthor #City/StateTeachers
S|CRS|ALTA84|10 Apr 2014|ALTA|Art of Living Happi
S|CRS|BC0037|12 Jun 2014|BC|Art of Living Happine
S|CRS|QUE087|19 Jun 2014|QUE|Art of Living Happin
S|CRS|ALTA95|05 Jun 2014|ALTA|Art of Living Happi

<colgroup><col><col span="2"><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
Course IdStart DateEnd DateCourse TypeCityStateTeacher(s)
BC002211/15/1311/19/13Course oneVancouverBritish ColumbiaMR XYZ
BC00023/28/133/31/13Art of SilenceBritish ColumbiaMS OPQ
BC002111/21/1311/24/13Course super oneVancouverBritish ColumbiaMR ABC
BC002012/18/132/21/14Fantastic courseChilliwackBritish ColumbiaMR EFG
ALTA8404/10/20144/14/14Art of Living Happiness ProgramCalgaryAlbertaMS HIJ
ALTA914/24/144/27/14Art of LivingEdmontonAlbertaMS LMN

<tbody>
</tbody>

<tbody>
</tbody>
Sheet1

DateAmountRevenue SourceNoteDeposit NumberAccount NumberCustomer IDAuthor #CityStateTeachers
S|CRS|ALTA84|10 Apr 2014|ALTA|Art of Living HappiCalgaryAlbertaMS HIJ
S|CRS|BC0037|12 Jun 2014|BC|Art of Living Happine
S|CRS|QUE087|19 Jun 2014|QUE|Art of Living Happin
S|CRS|ALTA95|05 Jun 2014|ALTA|Art of Living Happi

<tbody>
</tbody>

<tbody>
</tbody>

Sheet2

on sheet2

Cell I2 =INDEX(Sheet1!E:G,MATCH(MID(D2,FIND(CHAR(7),SUBSTITUTE(D2,"|",CHAR(7),2))+1,6),Sheet1!A:A,0),1)

Cell J2 =INDEX(Sheet1!E:G,MATCH(MID(D2,FIND(CHAR(7),SUBSTITUTE(D2,"|",CHAR(7),2))+1,6),Sheet1!A:A,0),2)

Cell K2 =INDEX(Sheet1!E:G,MATCH(MID(D2,FIND(CHAR(7),SUBSTITUTE(D2,"|",CHAR(7),2))+1,6),Sheet1!A:A,0),3)

the way it works is to find the Course ID from Sheet2 Column D with

-MID(D2,FIND(CHAR(7),SUBSTITUTE(D2,"|",CHAR(7),2))+1,6)
this is the six letters after the second of | in the Column D Note.
-then use the MATCH function to find the location of that ID in Sheet1 Column A
- once the match found, use INDEX to locate the info required, i.e row/column within Columns E:G.

MATCH will give the row of the matched ID, and columns 1,2 & 3 are the info required
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

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