求助：多IF嵌套INDEX/MATCH实现植被数据库跨表自动填充

Hey there! Let's work through this formula challenge you're facing with your vegetation database—sounds like you're trying to auto-populate a main table from other identically structured sheets based on the "Study Type" in column D, and nesting IF with INDEX/MATCH is giving you trouble. I’ve been in similar spots with ArcGIS-ready databases, so let’s break this down step by step.

First: Simplify with INDIRECT (If Study Types Match Sheet Names) #

If your worksheet names exactly match the values in column D (e.g., D2 = "Field Survey" and you have a sheet named Field Survey), you can skip messy nested IFs entirely by using INDIRECT to dynamically reference the right sheet.

For example, if you want to pull the "Vegetation Name" from the matching sheet (assuming your main table uses column A as a unique ID to match across sheets), use this formula in your main table's "Vegetation Name" column (say, cell B2):

=INDEX(INDIRECT("'"&$D2&"'!B:B"), MATCH($A2, INDIRECT("'"&$D2&"'!A:A"), 0))

• INDIRECT("'"&$D2&"'!B:B") converts the text in D2 into a valid sheet reference (the single quotes handle sheet names with spaces).
• MATCH($A2, ..., 0) finds the row in the target sheet where the ID matches your main table's ID.
• The $ locks the column references so you can drag the formula down/across without breaking it.

If You Need Nested IF for Specific Mapping #

If your study types don’t directly match sheet names (e.g., "Remote Sensing" maps to sheet RS_Data), nested IFs work—just keep them concise. Here’s an example for 2 study types:

=IF($D2="Remote Sensing", INDEX(RS_Data!B:B, MATCH($A2, RS_Data!A:A, 0)), 
 IF($D2="Field Survey", INDEX(Field_Survey!B:B, MATCH($A2, Field_Survey!A:A, 0)), 
 "No matching data"))

You can add more IF layers as needed, but if you have 5+ study types, this gets unwieldy (see the pro tip below).

Common Pitfalls to Avoid #

• Sheet names with spaces: Always wrap sheet names in single quotes ('Sheet Name'!A:A)—the INDIRECT example above does this automatically.
• Non-unique IDs: MATCH will return the first matching row, so make sure your ID column (column A in the example) has unique values across all sheets.
• Error handling: Add IFERROR to clean up #N/A or #REF! errors when no match exists:

  =IFERROR(INDEX(INDIRECT("'"&$D2&"'!B:B"), MATCH($A2, INDIRECT("'"&$D2&"'!A:A"), 0)), "No data")
  

Pro Tip for Scalable Setups #

If you have lots of study types, create a mapping sheet (e.g., named Sheet_Map) where column A is the study type and column B is the corresponding sheet name. Then use VLOOKUP with INDIRECT to avoid nested IFs entirely:

=INDEX(INDIRECT("'"&VLOOKUP($D2, Sheet_Map!$A:$B, 2, 0)&"'!B:B"), MATCH($A2, INDIRECT("'"&VLOOKUP($D2, Sheet_Map!$A:$B, 2, 0)&"'!A:A"), 0))

This way, you just add rows to Sheet_Map when you add new study types/sheets—no formula edits needed.

内容的提问来源于stack exchange，提问作者Nathan Dulfon