Columns A and B represent a range. Trying to write a formula that will look up the amount in Column F, to where it falls between (or equal to) A and B, and return the % from Column C into Column G.

In G2 , use a range-match formula like this: =XLOOKUP(1,((F2>=$A$2:$A$100)*(F2<=$B$2:$B$100)),$C$2:$C$100) Then fill the formula down column G . How it works: F2>=$A$2:$A$100 checks whether the value in F2 is greater than or equal to the start of each range. F2<=$B$2:$B$100 checks whether it is less than or equal to the end of each range. Multiplying those two tests returns 1 only for the row where F2 falls within the range. XLOOKUP returns the matching percentage from column C . If the value in F2 might not match any range, use: =IFERROR(XLOOKUP(1,((F2>=$A$2:$A$100)*(F2<=$B$2:$B$100)),$C$2:$C$100),"") An equivalent alternative is: =FILTER($C$2:$C$100,(F2>=$A$2:$A$100)*(F2<=$B$2:$B$100),"") Selecting a value that fits between two values - Microsoft Q&A Use cell references in a formula Overview of formulas in Excel

Need help with writing an Excel formula

Answer accepted by question author

Jay1 Tran 600 Independent Advisor

Hi MargieA,

Thank you for reaching out.

From what you've shared, the formula provided by the AI is correct. However, each value in Column A contains a trailing hyphen (-), which causes Excel to treat the values as text rather than numbers. As a result, the formula will fail when attempting to compare the value in Column F against the ranges in Columns A and B.

To make the formula work, please remove the trailing hyphens from Column A. If you need to keep the hyphens, please reach out again, and I’ll be happy to help modify the formula accordingly.

Thank you for your time and patience.

MargieA 40 Reputation points

2026-09-01T16:58:40.0033333+00:00

Thanks! I removed the hyphens and got it to work. I do have another question though ... the formula has the range to search ending in row 100 ... right now my range ends at row 81, so I changed the 100 to 81. Is there a way to adjust the formula in case more rows are added beyond 81 so that it doesn't error?
Jay1 Tran 600 Reputation points Independent Advisor

2026-09-01T17:51:56.2066667+00:00
Hi MargieA,

Thank you for your response.

To ensure future entries are included in the lookup, you have two options:

Option 1: Convert the range into an Excel Table

Select the data in Columns A through C.

Go to Insert > Table, or press Ctrl + T.

Confirm the range and select My table has headers.

Update the formula to use the Table columns, such as: =IFERROR(XLOOKUP(1,(F2>=Table1[EIA PRICE Start])*(F2<=Table1[EIA PRICE End]),Table1[WF FSC]),"")

Option 2: Extend the current lookup ranges

You can keep the data as a regular range and increase the ending row in the formula. For example, if the formula currently references:

$A$2:$A$10

$B$2:$B$10

$C$2:$C$10

You can extend the references to allow for future entries:

$A$2:$A$500

$B$2:$B$500

$C$2:$C$500

This will allow the formula to check future entries through row 500. All referenced ranges should remain the same size.

I hope this information clarified your concern. Thank you for your time and patience.
Jay1 Tran 600 Reputation points Independent Advisor

2026-09-02T23:00:16.76+00:00

Hi MargieA,

Have you had a chance to review the information provided? Any updates would be greatly appreciated.
MargieA 40 Reputation points

2026-09-03T12:54:21.0366667+00:00

Thank you. Looks like the easiest solution is just to extend the reference.
Jay1 Tran 600 Reputation points Independent Advisor

2026-09-03T21:26:58.5066667+00:00

Hi MargieA,

Thank you for your response.

You’re welcome. Extending the reference is the simplest and most practical solution.

Answer accepted by question author

AI answer

In G2, use a range-match formula like this:

=XLOOKUP(1,((F2>=$A$2:$A$100)*(F2<=$B$2:$B$100)),$C$2:$C$100)

Then fill the formula down column G.

How it works:

F2>=$A$2:$A$100 checks whether the value in F2 is greater than or equal to the start of each range.
F2<=$B$2:$B$100 checks whether it is less than or equal to the end of each range.
Multiplying those two tests returns 1 only for the row where F2 falls within the range.
XLOOKUP returns the matching percentage from column C.

If the value in F2 might not match any range, use:

=IFERROR(XLOOKUP(1,((F2>=$A$2:$A$100)*(F2<=$B$2:$B$100)),$C$2:$C$100),"")

An equivalent alternative is:

=FILTER($C$2:$C$100,(F2>=$A$2:$A$100)*(F2<=$B$2:$B$100),"")

AI-generated content may be incorrect. Read our transparency notes for more information.

Need help with writing an Excel formula

1 additional answer

Your answer