Jan 25 2022 08:37 AM
Hello,
I have a spreadsheet with cells B11 through B65 with a 4 digit part number. Example, 4112. If a user enters a part number in cells B72 through B150 but that part number is not a valid part found in cells B11 through B65 it will display Invalid Part in that field.
How can this be done?
Steph
Jan 25 2022 08:54 AM
=IFERROR(VLOOKUP(B72,$B$11:$B$65,1,FALSE),"Invalid Part")
It works in my spreadsheet if i enter this formula in cell C72 and copy it down to cell C150. An entry in cells B72:B150 either returns the item number or Invalid Part.
Jan 25 2022 09:20 AM
Jan 25 2022 09:40 AM
Solution=IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part")
This works in my spreadsheet. Unfortunately from your initial post i didn't understand that there are part descriptions in range C11:C65 which you want to retrieve.
Jan 25 2022 09:44 AM
Jan 25 2022 09:52 AM
Jan 25 2022 10:06 AM
=IF(ISBLANK(B72),"",IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part"))
I entered this formula in C72 and copied it down to C150 and it works in my sheet.
Jan 25 2022 10:11 AM
The display shown in the image was produced by conditional formatting.
In each case the typed number is still there but the number format shows the additional text.
The formula used to trigger the conditional format is
= COUNTIFS(validNumbers, currentCell)=0
where 'currentCell' is a relative reference to the active cell and the number format is
"Invalid part" 0
(the 0 may be omitted to hide the 4-digit code).
If the user-provided data is to be used elsewhere, the formula
= IF(
COUNTIFS(validNumbers, userInput),
userInput,
NA()
)
will error where the codes are invalid.
Jan 25 2022 09:40 AM
Solution=IFERROR(VLOOKUP(B72,$B$11:$C$65,2,FALSE),"Invalid Part")
This works in my spreadsheet. Unfortunately from your initial post i didn't understand that there are part descriptions in range C11:C65 which you want to retrieve.