Formula to compare two values using prefix/suffix

[Aswinraj11]

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]

NBVC thank you it works

[NBVC]

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,"")

[Aswinraj11]

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

[Abingail]

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

[NBVC]

Do you mean?

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