How to pull information from a cell based on two or more cells content?

I have a main Spreadsheet, where I'm pulling information from other various spreadsheets, as of now I have this formula:

=IF(ISBLANK(F2), "", SUMIF('Redirects Sheets'!A$2:A$19499, "*" & F2 & "*", 'Redirects Sheets'!B$2:B$19499))

if the content in the cell F2 matches what's in the redirect sheet, it would pull the information.

What I want to add to this is in case that the information in F2 does not match the content from the redirect sheet, to look into another spreadsheet for the information matching what I have in A2 which is a simple name, and then fill the cell with that information.

I tried different ifs or sum if but I get limited in the way google sheets handles conditionals, or maybe and super new to this and I just don't get the proper way of doing this.

when testing or nesting other IFs, or sumifs, I get parse errors, or #N/A .