NEED FORMULA/FUNCTION IN 3rd SHEET TO LOOK_UP ARRAY IN 2 SHEETS

[excelonguy]

Hi, everybody I am new to this forum and this is my first thread.

Sir, I am having a problem in formula input in Sheet3.(excel file is attached)
I have 3 sheets, I want to compare column A of Sheet3 with (Sheet1+Sheet2) because data in column A of Sheet1 is in continuation with Sheet2.
And I want to return the value of column C from (Sheet1+Sheet2) as data is in continuation.

Please Login or Register  to view this content.

But this code only helps me to return value from Sheet1 and not from Sheet2.
Hence, I need to compare data of Sheet3 with both the first two sheets and need the formula/function to return value by looking up array in both Sheet1+Sheet2.
Please provide solution!!!

[:) Sixthsense :)]

In D2 Cell of Sheet3

=IF(A2="","",IFERROR("Sheet1:"&VLOOKUP(A2,Sheet1!A:C,3,FALSE),"Sheet1:NotFound")&" | "&IFERROR("Sheet2:"&VLOOKUP(A2,Sheet2!A:C,3,FALSE),"Sheet2:NotFound"))

Drag it down...

[excelonguy]

In D2 Cell of Sheet3

=IF(A2="","",IFERROR("Sheet1:"&VLOOKUP(A2,Sheet1!A:C,3,FALSE),"Sheet1:NotFound")&" | "&IFERROR("Sheet2:"&VLOOKUP(A2,Sheet2!A:C,3,FALSE),"Sheet2:NotFound"))

Drag it down...

Thankyou Sir, for your expert advice but Sir, I am getting the value as

Please Login or Register  to view this content.

in cell D2 of Sheet3.
Your formula is absolutely corect but Sir, I need only

Please Login or Register  to view this content.

as value in cell D2 of Sheet3 i.e. without "Sheet1: | Sheet2:NotFound"
Simliarly , for the other cells too when I drag the formula down.
Waiting for your reply.

[:) Sixthsense :)]

To Lookin Sheet1

=IFERROR(VLOOKUP(A2,Sheet1!A:C,3,FALSE),"")

To Lookin Sheet2

=IFERROR(VLOOKUP(A2,Sheet2!A:C,3,FALSE),"")