Assistance With Formula Needed

[meppwc]

Would someone please assist me with a formula that performs the following:

Formula will reside in cell B2 of worksheet “ORACLE_Y”
Lookup value in cell A2 of “ORACLE_Y” worksheet, search for that value in column B of “ASSET LIST” worksheet.
If value is found, map back the value found in column A of the “ASSET LIST” worksheet.
If value is found but the cell in “ASSET LIST” is blank, say “PRESENT IN AL BUT STATUS UNKNOWN”
If the value searched on is NOT found, then say “MSN NOT FOUND IN AL”.

 

[etaf]

=IFERROR( IF(INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0))=0,"PRESENT IN AL BUT STATUS UNKNOWN", INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0)) ), "MSN NOT FOUND IN AL")

Book10
	A	B
1
2	1	PRESENT IN AL BUT STATUS UNKNOWN
3		MSN NOT FOUND IN AL
4		MSN NOT FOUND IN AL
5	2	fred
ORACLE_Y

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR( IF(INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0))=0,"PRESENT IN AL BUT STATUS UNKNOWN", INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0)) ), "MSN NOT FOUND IN AL")

Book10
	A	B
1
2
3
4		1
5
6	fred	2
ASSET LIST

 

[meppwc]

I apologize, I am very confused. Is there not just one formula that I can insert into that cell that will accomplish what I need, without the need to an additional table (if I understand correctly)? If I place the following formula in the call, I get a result that is not true
=IFERROR( IF(INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0))=0,"PRESENT IN AL BUT STATUS UNKNOWN", INDEX('ASSET LIST'!$A$2:$A$22,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$16,0)) ), "MSN NOT FOUND IN AL")

 

[etaf]

dont understand

Is there not just one formula that I can insert into that cell that will accomplish what I need, without the need to an additional table

what table

i just made an example ?
Asset list as your example , based on the text you mentioned

Lookup value in cell A2 of “ORACLE_Y” worksheet, search for that value in column B of “ASSET LIST” worksheet.
If value is found, map back the value found in column A of the “ASSET LIST” worksheet.
If value is found but the cell in “ASSET LIST” is blank, say “PRESENT IN AL BUT STATUS UNKNOWN”
If the value searched on is NOT found, then say “MSN NOT FOUND IN AL”.

Like Quote Reply

SO the range may need changing - rows etc

=IFERROR( IF(INDEX('ASSET LIST'!$A$2:$A$10000,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$10000,0))=0,"PRESENT IN AL BUT STATUS UNKNOWN", INDEX('ASSET LIST'!$A$2:$A$10000,MATCH(ORACLE_Y!A2,'ASSET LIST'!$B$2:$B$10000,0)) ), "MSN NOT FOUND IN AL")

 

[meppwc]

Gotcha.............I got it to work. I appreciate your assistance very much

 

[etaf]

you are welcome