Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
3.8k views
in Technique[技术] by (71.8m points)

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

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?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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))

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...