Excel formula.

Question

Excel formula.

steven dowsett 0

In excel, I use a formula. I want to reference values that have two properties, one is a value (%) used in a calculation (formula) the other needs some recognition so the formula can be copied down. Here is an example: A (apples) are 20%, B (bananas) are 30%, C (carrots) are 10%, D (dates) are 5%, E (eggplants) are 2%, F (figs) are 10%, etc. The problem I have is I need to reference the E (eggplants) = ?% for the formula, although any given day I receive these % it could different, A,F,E,D or on another day it could be D,F,EA etc. I need the formula to see the two things in order to do the calculations and be copied down. Would a formula like VLookup or Index and Match be a viable solution. Any help would greatly appreciated. Thank you Steven D.

Parth Parmar 0 Reputation points

2026-07-08T11:09:09.4366667+00:00

Hello @steven dowsett

I think there might be a little more to your question than just looking up the percentage.

If the order of A, B, C, D, E, etc. changes every day, then yes, using XLOOKUP (or INDEX/MATCH if you're on an older version of Excel) is the right idea because it looks for the item itself rather than where it happens to be in the list.

The part I'm not clear on is when you say the formula needs to "see two things." At the moment it sounds like there's another condition involved besides the item code, but it's hard to tell what that is.

Could you post a small sample of your data and the formula you're using now? Also show what result you're expecting. That will make it much easier to tell whether a simple lookup will do the job or whether you need a lookup based on multiple conditions. Thanks.

1 answer

Your answer

Parth Parmar 0 Reputation points

2026-07-08T11:09:09.4366667+00:00

Hello @steven dowsett

I think there might be a little more to your question than just looking up the percentage.

If the order of A, B, C, D, E, etc. changes every day, then yes, using XLOOKUP (or INDEX/MATCH if you're on an older version of Excel) is the right idea because it looks for the item itself rather than where it happens to be in the list.

The part I'm not clear on is when you say the formula needs to "see two things." At the moment it sounds like there's another condition involved besides the item code, but it's hard to tell what that is.

Could you post a small sample of your data and the formula you're using now? Also show what result you're expecting. That will make it much easier to tell whether a simple lookup will do the job or whether you need a lookup based on multiple conditions. Thanks.

Answer 1

Hello @steven dowsett

Based on your description, it sounds like you have a list of items in one column and their corresponding percentages in another, and you want a formula that can return the correct percentage based on an item reference while still working when copied down.

Assuming:

Column A contains the item names (Apples, Bananas, Carrots, etc.)
Column B contains the percentages
Cell D2 contains the item you want to look up (for example, "Eggplants")

You could use one of the following formulas:

XLOOKUP (Microsoft 365 / Excel 2021+): =XLOOKUP(D2,$A$2:$A$100,$B$2:$B$100,"Not found") This searches for the value in D2 within A2:A100 and returns the matching percentage from B2:B100.
VLOOKUP: =VLOOKUP(D2,$A$2:$B$100,2,FALSE) This looks for the value in D2 in the first column of the table and returns the corresponding percentage from the second column. The FALSE argument ensures an exact match.
INDEX + MATCH: =INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100,0)) MATCH finds the position of the item, and INDEX returns the percentage from the corresponding row.

User's image

If you plan to copy the formula down, make sure to use absolute references (the $ signs, which you can add by pressing F4) so that the lookup range remains fixed as the formula is filled down.

If I've misunderstood your scenario, could you provide a small sample of your data along with the expected result? That would help the community suggest the most appropriate formula.

Hope this helps. Feel free to follow up with additional details if needed.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Gabriel-N 19,885 Reputation points Microsoft External Staff Moderator

2026-07-08T10:20:41.3933333+00:00

Hello @steven dowsett

May I know if you have got any chance to check my answer? I am glad to help if you have any other questions