Lookup
I am looking in a particular column for names and a want to return the data
below the name to a second sheet. I have used =MATCH(D1,Apr!$AG$1:$AG$658,0) which will return the row number where the lookup name appears. I have also used the index function to return the information in the cell but doing it that way I would have to change the formula in each cell downward. I know there is a better way. Thanks for any help. Sheet 1 Column A Name1 data1 data2 data3 Name2 data1 data2 data3 Sheet 2 A B Name1 Name 2 data1 data1 data2 data2 data3 data3 |
Lookup
Assuming that the format is consistent, try...
A1, copied down and across: =INDEX(Sheet1!$A$1:$A$8,(ROWS(A$1:A1)-1)+(COLUMNS($A1:A1)*4-4+1)) Hope this helps! In article , hcesc wrote: I am looking in a particular column for names and a want to return the data below the name to a second sheet. I have used =MATCH(D1,Apr!$AG$1:$AG$658,0) which will return the row number where the lookup name appears. I have also used the index function to return the information in the cell but doing it that way I would have to change the formula in each cell downward. I know there is a better way. Thanks for any help. Sheet 1 Column A Name1 data1 data2 data3 Name2 data1 data2 data3 Sheet 2 A B Name1 Name 2 data1 data1 data2 data2 data3 data3 |
Lookup
I tried to figure out how I could get it to work for my situation but but was
unsuccessful. This is what I have but it isn't working: =INDEX(Apr!$AG$1:$AG$658,MATCH(B$1,Apr!$AG$1:$AG$6 58,0)-1)+(COLUMNS($AG1:AG1)*4-4+1)) I am doing a Match to find the row number of the Name I am searching for because they will not be in the same order in both sheets. Once I find the name the data would be in the same order from sheet to sheet. Also "Domenic" wrote: Assuming that the format is consistent, try... A1, copied down and across: =INDEX(Sheet1!$A$1:$A$8,(ROWS(A$1:A1)-1)+(COLUMNS($A1:A1)*4-4+1)) Hope this helps! In article , hcesc wrote: I am looking in a particular column for names and a want to return the data below the name to a second sheet. I have used =MATCH(D1,Apr!$AG$1:$AG$658,0) which will return the row number where the lookup name appears. I have also used the index function to return the information in the cell but doing it that way I would have to change the formula in each cell downward. I know there is a better way. Thanks for any help. Sheet 1 Column A Name1 data1 data2 data3 Name2 data1 data2 data3 Sheet 2 A B Name1 Name 2 data1 data1 data2 data2 data3 data3 |
Lookup
A little unclear, but assuming that B1, C1, D1, etc., contain the name,
try... B2, copied down and across: =INDEX(Apr!$AG$1:$AG$658,MATCH(B$1,Apr!$AG$1:$AG$6 58,0)+ROWS(B$2:B2)) Hope this helps! In article , hcesc wrote: I tried to figure out how I could get it to work for my situation but but was unsuccessful. This is what I have but it isn't working: =INDEX(Apr!$AG$1:$AG$658,MATCH(B$1,Apr!$AG$1:$AG$6 58,0)-1)+(COLUMNS($AG1:AG1)* 4-4+1)) I am doing a Match to find the row number of the Name I am searching for because they will not be in the same order in both sheets. Once I find the name the data would be in the same order from sheet to sheet. |
Lookup
That worked, thanks alot!
"Domenic" wrote: A little unclear, but assuming that B1, C1, D1, etc., contain the name, try... B2, copied down and across: =INDEX(Apr!$AG$1:$AG$658,MATCH(B$1,Apr!$AG$1:$AG$6 58,0)+ROWS(B$2:B2)) Hope this helps! In article , hcesc wrote: I tried to figure out how I could get it to work for my situation but but was unsuccessful. This is what I have but it isn't working: =INDEX(Apr!$AG$1:$AG$658,MATCH(B$1,Apr!$AG$1:$AG$6 58,0)-1)+(COLUMNS($AG1:AG1)* 4-4+1)) I am doing a Match to find the row number of the Name I am searching for because they will not be in the same order in both sheets. Once I find the name the data would be in the same order from sheet to sheet. |
All times are GMT +1. The time now is 03:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com