Help with array formula
Tbonekiller
Posts
17
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
June 6, 2024
-
Updated on Jun 6, 2024 at 11:26 AM
BoBot Posts 297 Registration date Wednesday October 11, 2023 Status Moderator Last seen October 31, 2024 - Jun 11, 2024 at 05:19 PM
BoBot Posts 297 Registration date Wednesday October 11, 2023 Status Moderator Last seen October 31, 2024 - Jun 11, 2024 at 05:19 PM
Related:
- Arrayformula
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Excel grade formula - Guide
- Number to words in excel formula - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Vba check if value is in array - Guide
1 response
BoBot
Posts
297
Registration date
Wednesday October 11, 2023
Status
Moderator
Last seen
October 31, 2024
9
Jun 11, 2024 at 05:19 PM
Jun 11, 2024 at 05:19 PM
Hello !
The AI Bobot is here to help you!
Your main issue seems to be the use of value function in the array formula, which is causing some matches to be missed out in case the part numbers or PO numbers have alphanumeric characters.
The VALUE function in Excel is used to convert numbers formatted as text to actual numbers, and it'll return an error if you use it on a cell which contains both text and numbers. In your case, both part numbers and PO numbers could contain letters, so you need to remove the VALUE function from the formula for those cells.
Also, to ensure no matches are missed, verify that both the cells being compared are in the same format. If one is text and the other is a number, Excel may not always register the match. Check the formatting on the original data and adjust if needed.
Remember to enter the formulas using Ctrl + Shift + Enter, because it's an array formula.
The AI Bobot is here to help you!
Your main issue seems to be the use of value function in the array formula, which is causing some matches to be missed out in case the part numbers or PO numbers have alphanumeric characters.
The VALUE function in Excel is used to convert numbers formatted as text to actual numbers, and it'll return an error if you use it on a cell which contains both text and numbers. In your case, both part numbers and PO numbers could contain letters, so you need to remove the VALUE function from the formula for those cells.
Also, to ensure no matches are missed, verify that both the cells being compared are in the same format. If one is text and the other is a number, Excel may not always register the match. Check the formatting on the original data and adjust if needed.
Remember to enter the formulas using Ctrl + Shift + Enter, because it's an array formula.