Nested If Statement that pulls data from multiple sheets not working in Google Sheets

Question

I am trying to pull together data from multiple sources (each is in a different sheet in the same workbook). I am first checking to see if there is a value in each of the 3 relevant sheets (there usually are not values for each unique ID). If there is, I want it to use that value. If there isn't, I want it to move on to the next sheet check. If none of these sheets has a value, I want a blank cell. Here is what I have so far:

= IF(NOT(ISNA(VLOOKUP($A29,coe,21,FALSE))),(VLOOKUP($A29,coe,21,FALSE)),
IF(NOT(ISNA(VLOOKUP($A29,fds,29,FALSE))),(VLOOKUP($A29,fds,29,FALSE)),
IF(NOT(ISNA(VLOOKUP($A29,fds,43,FALSE))),(VLOOKUP($A29,fds,43,FALSE)),
IF(NOT(ISNA(VLOOKUP($A29,meeg,6,FALSE))),(VLOOKUP($A29,meeg,6,FALSE)),
""))))

I'm finding that the information from the 3rd line is not showing up when it should. But if I delete the first or second If statement, it shows up fine. I think all of my parenthesis are in the right spot, but I can't think of anything else to check at this point. Any ideas?

Answer 1

try:

=IFNA(IFNA(IFNA(IFNA(
 VLOOKUP($A29, coe, 21, 0), 
 VLOOKUP($A29, fds, 29, 0)), 
 VLOOKUP($A29, fds, 43, 0)), 
 VLOOKUP($A29, meeg, 6, 0)))

or:

 IFNA(VLOOKUP($A29, {
 INDEX(coe ,,1), INDEX(coe,,21); 
 INDEX(fds ,,1), INDEX(fds,,29); 
 INDEX(fds ,,1), INDEX(fds,,43); 
 INDEX(meeg,,1), INDEX(meeg,,6)}, 2, 0))