Google spreadsheet to web collaboration O365 formulas migration

[Anto399]

I All , i would need help in use same effect formula used in Google spreadsheet into O365 web collaboration excel.
Below the current working Google formula :

=IFERROR(IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(J4,Italy!A:G,7,FALSE),VLOOKUP(J4,Italy!B:G,6,FALSE)),VLOOKUP(J4,Italy!C:G,5,FALSE)),VLOOKUP(J4'Part No./Drops'!A:B,2,FALSE)),"no match"))


Basically i have an excel with a tab named Italy where i have db of all hardware(asset tag column A , IMEI column B , serial number column C.
There is also another tab named "Part No./Drops" where there is db of non-asset tagged items (e.g. consumables).
Then there is a tab(this is the one which i need the formula for) where i need to track in/out flow which have auto-fill function based on asset tag/IMEI/Serial number/part number.
I need a single formula into "Item auto-fill" column into in/out flow tab which lookup into Italy tab for Asset tag value (J4,Italy!A:G,7,FALSE) , if error/NA look for IMEI (J4,Italy!B:G,6,FALSE)) , if error/NA look for serial number (J4,Italy!C:G,5,FALSE)) , if error/NA look for part number into Part No./Drops tab (J4'Part No./Drops'!A:B,2,FALSE)) and finally if error/NA for all the vlookup return "no match".
Briefly i would need a single formula which lookup for a value into different columns on Italy tab and into Part No./Drops tab at same time.
I hope i have explained clearly my need and available to provide further.
Please find attached a screenshot of in subject excel spreadsheet.

Thanks

Anto

 

[Anto399]

Hallo, any help from somebody please?

 

[Anto399]

I'm very close to the solution with the below :

=IFERROR(VLOOKUP(J4,Italy!A:G,7,0),IFERROR(VLOOKUP(J4,Italy!B:G,6,0),VLOOKUP(J4,Italy!C:G,5,0)))
But there are still missing two if error vlookup for J4'Part No./Drops'!A:B,2,FALSE and No Match if the value in J4 is actually not found by all the vlookup.
I tried to add another iferror vlookup nested as formula above for 4'Part No./Drops'!A:B,2,FALSE , but seems excel doesn't accept it... maybe i'm missing some parenthesis?
Is there a limit for nested if error vlookup?
Finally i still would need a final if error vlookup which returns "NO MATCH" if J4 value is actually not found by all vlookup.

Thanks

 

[Fluff]

This Part No./Drops is not a valid sheet name in Excel

 

[Anto399]

Almost done!

=IFERROR(VLOOKUP(J4,'Part No.&Drops'!A:B,2,0),IFERROR(VLOOKUP(J4,Italy!A:G,7,0),IFERROR(VLOOKUP(J4,Italy!B:G,6,0),VLOOKUP(J4,Italy!C:G,5,0))))

Just missing the last "NO MATCH" iferror...

HELP?

 

[Anto399]

This Part No./Drops is not a valid sheet name in Excel

Yes thanks Fluff , i've changed into "Part NO.&Drops and with the last formulas i've published in my last post is working.
Just missing the last "NO MATCH" !
Any suggestions?

Thx

 

[Fluff]

Try wrapping the whole thing in another iferror

 

[Anto399]

Try wrapping the whole thing in another iferror

Thanks, could you please write down what you mean applied to my formula?
Sorry I'm not so expert ..

 

[Fluff]

Just add another iferror like
=iferror(your formula,"No match")

 

[Anto399]

Seems i've done :

=IFNA(IFERROR(VLOOKUP(J4,'Part No.&Drops'!A:B,2,FALSE),IFERROR(VLOOKUP(J4,Italy!A:G,7,FALSE),IFERROR(VLOOKUP(J4,Italy!B:G,6,FALSE),VLOOKUP(J4,Italy!C:G,5,FALSE)))),"NO_MATCH")

should be correct , isn't it?

thanks