Hello,
I cannot post my data, but I will try my best to explain what I am looking for.
I have multiple columns in Sheet1 with ID numbers.
In Sheet 2 I have a cell that has multiple iid's.
I need to conduct a search between the cell value in Sheet2 against all columns in Sheet1 with IDs. On top of that, once it finds a match, I need to ensure that the business name in another column from Sheet1 is brought over to Sheet2.
Here is the data.
Sheet 1:
The lookup columns are P, R, T, V, X, Z, AB, AD, AF, and AH ...and AK
Sheet2:
Column O is the cell that has multiple IDs. all IDs have a "- " prior to the number, so this is what i am currently using to search the field for multiple IDs.
Columns R thru AE is where I need a formula that will find the ID numbers from the cell in column O. I am not sure how to be able to look up multiple columns from Sheet1 so I created a section like R thru AE next to column AE for each column I need to lookup (10 sections total for each column). So the picture shown below is a lookup against column AH from Sheet1.
The current formula I have in cell R4 is
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("- "&VALUE(Sheet1!$AH$2:$AH$16430),'Sheet2'!$O4))))>0,IF(COUNTIF(Sheet1!$AK$2:$AK$16430,'Sheet2'!R$3),"X",""),"")
The countif section is not working for me as I thought it would because what I need is to mark each column with X if the header is a match to column AK in sheet1 when the ID is a match.
Can anyone help with this formula to mark the corresponding header with an X if the ID is a match in column AK
I hope this makes sense.
Below are the tables again if you want to copy and paste into your excel to assist.
Sheet2:
Sheet1:
Thanks for the help.
I cannot post my data, but I will try my best to explain what I am looking for.
I have multiple columns in Sheet1 with ID numbers.
In Sheet 2 I have a cell that has multiple iid's.
I need to conduct a search between the cell value in Sheet2 against all columns in Sheet1 with IDs. On top of that, once it finds a match, I need to ensure that the business name in another column from Sheet1 is brought over to Sheet2.
Here is the data.
Sheet 1:
The lookup columns are P, R, T, V, X, Z, AB, AD, AF, and AH ...and AK
Sheet2:
Column O is the cell that has multiple IDs. all IDs have a "- " prior to the number, so this is what i am currently using to search the field for multiple IDs.
Columns R thru AE is where I need a formula that will find the ID numbers from the cell in column O. I am not sure how to be able to look up multiple columns from Sheet1 so I created a section like R thru AE next to column AE for each column I need to lookup (10 sections total for each column). So the picture shown below is a lookup against column AH from Sheet1.
The current formula I have in cell R4 is
=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("- "&VALUE(Sheet1!$AH$2:$AH$16430),'Sheet2'!$O4))))>0,IF(COUNTIF(Sheet1!$AK$2:$AK$16430,'Sheet2'!R$3),"X",""),"")
The countif section is not working for me as I thought it would because what I need is to mark each column with X if the header is a match to column AK in sheet1 when the ID is a match.
Can anyone help with this formula to mark the corresponding header with an X if the ID is a match in column AK
I hope this makes sense.
Below are the tables again if you want to copy and paste into your excel to assist.
Sheet2:
| Column AH | ||||||||||||||
| EO&T | GCB | ICG | ||||||||||||
| Managed Segment (Home)- NodeNumber | CTI | EO&T Chief Administrative Office | CSS | CISO | Chief Operating Office | EIO&T Change Management | O&T Other | GFT | Operational Effectiveness | Business Simplification | GCB Operations | GCB Technology | ICG Operations | ICG Technology |
Capital Planning [L6](9920988) - 9920988|CCA Controllers [L8](5017) - 5017|Public Side Credit Trading [L8](20205) - 20205|Finance and Risk Shared Services [L6](9908260) - 9908260|Investments Pensions and Convergence Risk [L7](4978) - 4978 | ||||||||||||||
Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury Allocations [L9](24712) - 24712|Treasury Allocations [L9](24712) - 24712 | ||||||||||||||
Treasury [L6](9905121) - 9905121|PU/CO - Treasury [L6](9821741) - 9821741|PU/CO - Treasury [L6](9821741) - 9821741|PU/CO - Treasury [L7](9821020) - 9821020|PU/CO - Treasury [L7](9821020) - 9821020 | ||||||||||||||
Controller [L6](5002) - 5002 | ||||||||||||||
Stress Testing [L6](9920988) - 9920988|Corp Regulatory Reporting & Advisory Group [L7](9921014) - 9921014|Finance and Risk Shared Services [L6](9908260) - 9908260|Treasury [L8](14817) - 14817|Tax [L6](9905112) - 9905112 | ||||||||||||||
Controller [L6](5002) - 5002|Controller [L6](5002) - 5002|Controller [L6](5002) - 5002 | ||||||||||||||
Treasury - Other ICG- Core [L6](16728) - 16728|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728 | ||||||||||||||
FP&A Admin [L7](18359) - 18359|Global Strategy [L7(5110) - 5110|Global FP&A COE [L7](18361) - 18361|Global FP&A COE [L7](18361) - 18361|Corporate Strategy [L7](14832) - 14832|FP&A GPO [L7](18309) - 18309|Global FP&A COE [L7](18361) - 18361 | ||||||||||||||
Capital Planning [L7](27816) - 27816 | ||||||||||||||
Other Global Functions & Corp Activities [L5](16827) - 16827|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Other Global Functions & Corp Activities [L5](16827) - 16827|Stress Testing [L6](9920988) - 9920988|Stress Testing [L6](9920988) - 9920988 | ||||||||||||||
Treasury - Other ICG - Other [L6](16738) - 16738|Controller [L6](5002) - 5002|Treasury - Other ICG - Other [L6](16738) - 16738|Controller [L6](5002) - 5002 | ||||||||||||||
Investments Pensions and Convergence Risk [L7](4978) - 4978 | ||||||||||||||
Treasury [L6](9905121) - 9905121 | ||||||||||||||
Other Global Functions & Corp Activities [L5](16827) - 16827|Treasury [L6](9905121) - 9905121 | ||||||||||||||
Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121 | ||||||||||||||
Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121 | ||||||||||||||
Controller [L6](5002) - 5002|Capital Planning [L7](27816) - 27816|Capital Planning / Forecasting and Analytics [L8](25670) - 25670|Real_Estate [L7](20198) - 20198 | ||||||||||||||
Controller [L6](5002) - 5002|Capital Planning [L7](27816) - 27816|Stress Testing [L6](9920988) - 9920988 | ||||||||||||||
Corporate Controller [L8](14791) - 14791|Capital Quality Assurance [L8](27331) - 27331|Corp Regulatory Reporting & Advisory Group [L8](5019) - 5019 | ||||||||||||||
Consumer Risk [L6](9914218) - 9914218 | ||||||||||||||
Treasury [L6](9905121) - 9905121|Capital Markets [L8](19051) - 19051|Funding [L8](19567) - 19567 | ||||||||||||||
Credit & Obligor Risk Analytics [L8](20211) - 20211|Credit & Obligor Risk Analytics [L8](20211) - 20211 | ||||||||||||||
ICG Risk [L6](9904974) - 9904974|Stress Testing [L6](9920988) - 9920988|ICG Risk [L6](9904974) - 9904974 | ||||||||||||||
Market Risk Reporting [L8](20218) - 20218|Global Market Risk [L6](9900517) - 9900517|Global Risk and Valuation [L7](23862) - 23862|Quantitative Risk and Stress Testing [L7](9904931) - 9904931|FRSS Management [L7](27803) - 27803|Reporting Operations [L7](23817) - 23817|Capital Planning [L7](27816) - 27816|Treasury GPO [L7](9338) - 9338|Global Market Risk [L6](9900517) - 9900517|Commodities [L6](1326) - 1326|Market Risk Reporting [L8](20218) - 20218 | ||||||||||||||
Total Citi [L1](1) - 1|Total Citi [L1](1) - 1|Total Citi [L1](1) - 1|Total Citi [L1](1) - 1|Total Citi [L1](1) - 1|Total Citi [L1](1) - 1 | ||||||||||||||
Controller [L6](5002) - 5002 | ||||||||||||||
Rates Trading Book [L7](9923019) - 9923019|Funding [L8](19567) - 19567|Rates Trading Book [L7](9923019) - 9923019 | ||||||||||||||
Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121|Treasury - Other ICG- Core [L6](16728) - 16728|Treasury [L6](9905121) - 9905121|Treasury [L6](9905121) - 9905121 | ||||||||||||||
Trading_Book [L7](20203) - 20203|Trading_Book [L7](20203) - 20203 |
Sheet1:
| MS Level 5 ID | MS Level 6 ID | MS Level 7 ID | MS Level 8 ID | MS Level 9 ID | MS Level 10 ID | MS Level 11 ID | MS Level 12 ID | MS Level 13 ID | MS Level 14 ID | Sector | Business Name |
20693 | 20497 | 22823 | 9924014 | NULL | NULL | NULL | NULL | NULL | NULL | GCB | GCB Technology |
23778 | 9924007 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | GF | |
6804 | 20484 | 9923967 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | EO&T Chief Administrative Office |
8163 | 9923959 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | CSS |
22939 | 24908 | 9909307 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Operations |
22941 | 26202 | 9909306 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Technology |
22941 | 26202 | 9909245 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Technology |
27825 | 6719 | 9909183 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | CTI |
8279 | 9908280 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | GF | |
8163 | 9908260 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | CSS |
27318 | 17786 | 9908250 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | Chief Operating Office |
16312 | 20483 | 7036 | 17881 | 9907063 | NULL | NULL | NULL | NULL | NULL | EO&T | EIO&T Change Management |
27317 | 9906753 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | GFT |
8163 | 9906642 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | EO&T | CSS |
22941 | 26206 | 9905979 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Technology |
22941 | 9905510 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Technology |
22939 | 21650 | 9904410 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | ICG | ICG Operations |
Thanks for the help.
