Announcement

Collapse
No announcement yet.

Formula to compare two values using prefix/suffix

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

  • Aswinraj11
    started a topic Formula to compare two values using prefix/suffix

    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

  • Aswinraj11
    replied
    NBVC thank you it works

    Leave a comment:


  • NBVC
    replied
    Assuming you meant column C has "GG" and that Book 1 is actually another workbook, with the data to pick up from column C is in Sheet1 of that workbook....

    =IF(AND(RIGHT(B2,3)="mer",A2="9G",LEFT(C2="GG")),'[Book 1.xslx]Sheet1!'C2,"")

    Leave a comment:


  • Aswinraj11
    replied
    NBVC Thanks dude, it works..,

    But i Missed to provide some informations, sorry.., Actually it needs to pickup the data from one sheet to another sheet.
    Please help..,


    Example:
    Sheet 1 Data:

    It contains 3 Columns A, B and C..,

    If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG"

    Sheet 2 Data


    If Last 3 Letters in Column B is "mer" and Column A is "9G" and "GG" then it has to pickup the data from Column C - Book 1.

    Like a Vlookup.




    With Regards,
    Aswinraj A

    Leave a comment:


  • Abingail
    replied
    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

    Leave a comment:


  • NBVC
    replied
    Do you mean?

    =IF(AND(RIGHT(A2,3)="UUF",B2="SG",LEFT(C2="SG")),D2,"")

    Leave a comment:

Working...
X