using lookup or similar

Question

using lookup or similar

Danusia Waite 20

I have 2 separated spreadsheets in a workbook. The first spreadsheet contains Column C with Codes listed and Column E wants to pick up a dollar value that is contained in the second spreadsheet corresponding to the same Account Code. The second spreadsheet Column A has the same Code as in the first spreadsheet and has a value either in Column D or in Column E. Which Formula Function should I use and what is the formula. I have tried XLOOKUP, VLOOKUP, MATCH and having errors such as N/A. "SPILL! and other. Please can someone help out

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Jack Dang (WICLOUD CORPORATION) 18,975 Microsoft External Staff Moderator

Hi @Danusia Waite ,

I understand how frustrating it can be to deal with #N/A or #SPILL! errors when trying to pull values from another sheet. Let’s break it down and get it working.

Issue Summary:

You have two sheets in the same workbook.
- Sheet1:
  - Column C: account codes
  - Column E: needs the corresponding dollar value
- Sheet2:
  - Column A: account codes
  - Column D or E: dollar value (it could be in either column)
You’ve tried XLOOKUP, VLOOKUP, MATCH, etc., but keep getting errors.

Step-by-Step Guidance:

Check that account codes match exactly
- Extra spaces or formatting differences can cause #N/A.
- You can use =TRIM(C2) in a helper column to remove extra spaces.
Decide which column to return from Sheet2
- If the value is always in Column D, the formula is straightforward.
- If the value could be in Column D or E, we need a formula that checks both.
Using XLOOKUP (recommended for modern Excel)
- If the value is always in Column D:
```
     =XLOOKUP(C2, Sheet2!A:A, Sheet2!D:D, "Not Found")
```
  This looks for the code in Sheet2 Column A and returns the value from Column D.
- If the value might be in Column D or E:
```
     =XLOOKUP(C2, Sheet2!A:A, Sheet2!D:D, XLOOKUP(C2, Sheet2!A:A, Sheet2!E:E, "Not Found"))
```
  This first tries Column D, and if no value is found, checks Column E.
Using VLOOKUP (older Excel versions)
- If the value is in Column D:
```
     =VLOOKUP(C2, Sheet2!A:D, 4, FALSE)
```
  VLOOKUP cannot easily check two columns, so you would need a helper column if the value could be in either D or E.
Common causes of errors
- #N/A: lookup value not found (extra spaces or mismatched formatting)
- #SPILL!: the formula tries to return multiple results but cannot fit in the target cell

I suggest using XLOOKUP if your Excel version supports it. It handles missing values gracefully and is easier to manage than VLOOKUP. Also, make sure the account codes in both sheets are consistent in formatting and free from extra spaces.

If this helps fix the issue and my answer was helpful - kindly follow the instructions here so others with the same problem can benefit as well.

Danusia Waite 20 Reputation points

2025-10-07T04:25:34.89+00:00

Thank you for getting back to me

I'm getting a #VALUE! in Column above . Getting the right values in each of the 3 XLOOKUP section to the right e.g. Lookup_value = 6110 etc., but the amount of 5,738.88 doesn't show up just the error. Kind regards

Danusia
Danusia Waite 20 Reputation points

2025-10-07T04:29:17.4466667+00:00

DOES IT MATTER IF THE SAME CODE IS NOT

IN THE 2ND SPREADSHEET
Danusia Waite 20 Reputation points

2025-10-07T04:49:03.0866667+00:00

I saw other help site that it was important to set up data in tables. I set the second spread sheet up as a table should this be done and do I for both spreadsheet for all of the data to go into tables. The information and data changes month by month so the spreadsheet needs to be more fluid so that data does not get missed.
Jack Dang (WICLOUD CORPORATION) 18,975 Reputation points Microsoft External Staff Moderator

2025-10-07T06:52:25.2266667+00:00
Hi @Danusia Waite ,

Thanks for the follow-up! Let me address your points clearly:

Why XLOOKUP may show #VALUE! – This usually happens if the code you’re looking for doesn’t exist in the second sheet, or if the column you want to get the value from isn’t aligned correctly.

Handling missing codes – If a code in Sheet1 isn’t in Sheet2, XLOOKUP normally returns #N/A. You can prevent this by using the if_not_found argument:

=XLOOKUP(C2, Sheet2!A:A, Sheet2!D:D, "Not Found")

Looking in two columns (D or E) – Since the dollar value might be in either Column D or Column E, you can combine XLOOKUP with IFNA so it checks Column D first, then Column E if needed:

=IFNA(XLOOKUP(C2, Sheet2!A:A, Sheet2!D:D), XLOOKUP(C2, Sheet2!A:A, Sheet2!E:E, "Not Found"))

Using tables for dynamic data – Converting Sheet2 to a table helps your formulas automatically include new rows added each month. It’s optional for Sheet1 but highly recommended for Sheet2.

Example using a table named DataTable:

=IFNA(XLOOKUP(C2, DataTable[Code], DataTable[ValueD]), XLOOKUP(C2, DataTable[Code], DataTable[ValueE], "Not Found"))

Tip: Place this formula in Column E of Sheet1 and drag it down for all your rows. It will automatically pull the correct dollar value from Sheet2 and handle missing codes without showing errors.

Simpler alternative: If you want a formula that automatically picks the first non-empty value from Column D or E without using IFNA, you can use:

=INDEX(DataTable[ValueD], MATCH(C2, DataTable[Code], 0)) + INDEX(DataTable[ValueE], MATCH(C2, DataTable[Code], 0))

This adds the values from D and E for the matching code; if one column is empty, it just returns the value from the other. It’s simple and handles monthly updates automatically.