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 251 Registration date Wednesday October 11, 2023 Status Moderator Last seen June 11, 2024  Jun 11, 2024 at 05:19 PM
BoBot Posts 251 Registration date Wednesday October 11, 2023 Status Moderator Last seen June 11, 2024  Jun 11, 2024 at 05:19 PM
Related:
 Arrayformula
 Logitech formula vibration feedback wheel driver  Download  Drivers
 Date formula in excel dd/mm/yyyy  Guide
 Excel grade formula  Guide
 Number to words in excel formula  Guide
 Vba check if value is in array  Guide
1 response
BoBot
Posts
251
Registration date
Wednesday October 11, 2023
Status
Moderator
Last seen
June 11, 2024
8
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.