Evaluating a range for text and replacing...

[Wrclark160]

I am using a personnel system that labels departments using numbers. I'm trying to efficiently evaluate the cells and if the number is present, replace it with the words.

I essentially want to evaluate A1:A5, and if it equals anything in B1:B5, then replace it with D1:D5

For example, if A1=B1 or B2 or B3 or B4 or B5, replace with corresponding value in D column.

further example, 123 would be replaced with gear dept, 891 would be replaced as spin dept.

I think I have attached a picture of the sheet, I have tried REPLACE,SUBSTITUE, and even messed around IF functions.

Any help would be greatly appreciated

Screenshot 2022-03-08 010739.jpg

[TMS]

You would need VBA to do that. Is that what you

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Glenn Kennedy]

With a reference table, you can use:


=IF(B2="","",VLOOKUP(B2,F:G,2,FALSE))

[TMS]

@Glenn: the OP said …

I essentially want to evaluate A1:A5, and if it equals anything in B1:B5, then replace it with D1:D5

[Glenn Kennedy]

Yes, I know... but quite often what is said is not what is meant!! I made my guess on the basis that it would be odd to have the same values in adjacent columns as a starting point.