Announcement

Collapse
No announcement yet.

Formula to compare two values using prefix/suffix

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Formula to compare two values using prefix/suffix



    Hi Guys,

    I need to Pickup the value based on certain condition - If Column A has suffix UUF - Column B has SG - Column C has Prefix SG then it needs to pickup the value from Column D.

    Kindly help please.
    A B C D
    1AJ39AA SG SGIN 4
    1AJ40AA SG SGIN 5
    1AJ41AA SG SGIN 5
    1DE47AA#UUF SG SGIN 6
    1DE48AA#UUF CN CN 7
    1DT93AA#AB0 CN CHN 8
    1DT93AA#ACJ CN SGIN 9
    1DT93AA#UUF CN SGIN 10
    1FU87AA#UUF CN CH 11
    1FU88AA#UUF CN CH 12
    1FV38AA#ABJ IN IN 13
    1FV38AA#UUF IN IN 14
    1FV39AA#ABJ IN IN 15
    1FV39AA#UUF IN IN 16
    1HC89AA#ABG IN IN 17
    1DT93AA#AB0 IN IN 18
    1HC89AA#ABG IN IN 19
    1DT93AA#AB1 IN IN 20
    1HC89AA#ABG IN SGIN 21

  • #2
    Do you mean?

    =IF(AND(RIGHT(A2,3)="UUF",B2="SG",LEFT(C2="SG")),D2,"")
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    Comment


    • #3


      I would use an advanced filter. Copy the exact headings into the next 4 blank columns and in the row below the criteria you want. Use <>UUF for A. Select the list range as the table, criteria range as these four new volumes and the row below.

      This will then allow you to quickly change the criteria without having to edit a formula

      Comment

      Working...
      X