search text in the cell reference in multiple sheet in same work book with condition

[RAMESHEVER4U]

I have multiple sheets total =24

i have a new sheet no 25
in which A1:A2000 contains text
need to search the text in A1 in sheet1 to sheet 24
in all the 24 sheets the Text will be in the column C only
also it need to return the value in F column of the respective text row found in column C

Eg:

'Verify_whether_L1_NM_V1' this is the text in A1 of sheet 25
This need to be checked in C column of all other sheets and if found return the value in the Column F of the founded row.
The F row will contain a drop down any of NORUN OR PASS OR FAIL OR N/A
Please help....

 

[mumps]

What is the name of the new sheet? Are all the values in A1:A2000 unique values or can there be duplicates? In the other 24 sheets, can there be duplicates of the search value in column C? Where do you want the value in column F returned?

 

[RAMESHEVER4U]

Thanks for asking.
new sheet name is sheet25
duplicates may be there in both
return value can be placed in sheet25 column c.

What is the name otof the new sheet? Are all the values in A1:A2000 unique values or can there be duplicates? In the other 24 sheets, can there be duplicates of the search value in column C? Where do you want the value in column F returned?

 

[mumps]

Because there may be duplicates in both, the only way that I can think of to make this work would be to find the value in column F for only the first occurrence of each value otherwise when you copy the value in column F to column C from more than one of the duplicate values, column C will constantly be overwritten resulting in only the last value being displayed. Would it work for you if we searched only for the first duplicate value?

 

[Aladin Akyurek]

I have multiple sheets total =24

i have a new sheet no 25
in which A1:A2000 contains text
need to search the text in A1 in sheet1 to sheet 24
in all the 24 sheets the Text will be in the column C only
also it need to return the value in F column of the respective text row found in column C

Eg:

'Verify_whether_L1_NM_V1' this is the text in A1 of sheet 25
This need to be checked in C column of all other sheets and if found return the value in the Column F of the founded row.
The F row will contain a drop down any of NORUN OR PASS OR FAIL OR N/A
Please help....

Create a range housing the relevant sheet names, that is, Sheet1, Sheet2, etc. Select this range and name the selection as SheetList via the Name Box.

Now invoke:

=VLOOKUP(A1,INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIF(INDIRECT("'"&SheetList&"'!C2:C300"),A1),SheetList))&"'!C2:F300"),4,0)

which you must confirm with control+shift+enter, not just enter.

 

[RAMESHEVER4U]

The below realy worked fine.
I need to modify the coulumn to be checked as B and the last values from 4th column to 5th column as F is 5th column from B.

Thanks for timing help.


=VLOOKUP(A1,INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIF(INDIRECT("'"&SheetList&"'!B2:B700"),A1),SheetList))&"'!B2:F700"),5,0)

Create a range housing the relevant sheet names, that is, Sheet1, Sheet2, etc. Select this range and name the selection as SheetList via the Name Box.

Now invoke:

=VLOOKUP(A1,INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIF(INDIRECT("'"&SheetList&"'!C2:C300"),A1),SheetList))&"'!C2:F300"),4,0)

which you must confirm with control+shift+enter, not just enter.

 

[Aladin Akyurek]

The below realy worked fine.
I need to modify the coulumn to be checked as B and the last values from 4th column to 5th column as F is 5th column from B.

Thanks for timing help.


=VLOOKUP(A1,INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIF(INDIRECT("'"&SheetList&"'!B2:B700"),A1),SheetList))&"'!B2:F700"),5,0)

You are welcome. Thanks for providing feedback.