Newbie..Help required with a formula


New Member
May 3, 2007
I am after some help trying to get a formula to work and being a Newbie to excel im coming across some problems along the way.
i have been looking at the posts to try and find a solution but unable to find anything that relates to what im trying to do.

I am trying to use data from Sheet 1 and record the results on sheet 2.
I have managed to do single lines on Sheet 1 and the formula seems to work, however when I try to look at the whole sheet I get a value error.

This is the data im using:
1 A001 Drop N/A Cream L783 1L N/A
2 A001 Lift Cream R557 1L N/A
3 B002 Lift Dream Chips R556 1L N/A
4 B002 Lift N/A Cream L666 2L N/A
5 A001 Drop split Cream L786 2L N/A
To be added to daily so data sheet will get longer

This is the formula im using to work on a single line on a single sheet which is working:

=IF(AND(K12="A001",L12="Drop",N12="Cream",P12="2L",O12=K25),"Yes","NO") This gives me YES.

This is the formula I tried to use all of the data and get a "value" error:

=IF(AND(K8:K12="A001:",L8:L12="Drop",N8:N12="Cream",P8:P12="1L",O8:O12=K22),"Yes","NO") This gives me a VALUE error.

This is wht I need to show on sheet 2 to encorporate all of the relevant data from sheet 1

1 1L 2L
2 L783 #VALUE!
3 L784
4 L785
5 L786 Yes
6 L787

I hope I have given enough explaination for what I am trying to do and appreciate any help anyone can give.

Sorry for the data supplied but I cant seem to paste a copy of my spreadsheet and I have spread It out but it dosnt seem to align.

Thanks in advance

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
The reason it's not working is because you're testing more than one cell per condition.

I imagine that you're perhaps looking for something like this:

Hope this helps,

Upvote 0
Hi Chris

That is working perfectly thank you for your time. i really appreciate your help.
Is there any way to tell the calcultion to ignore any blank cells as it is showing a dropdown arrow in the corner which refers to an empty cell when there is one.
i have filled in all empty cells but didnt know if there was a way to add it to the calculation.

Once again many thanks

Upvote 0
You're welcome.

I'm not sure what you mean. Can you give an example of a cell that might be empty, i.e. where it is, and what effect you'd like it to have on the result of the formula?

Upvote 0
Hi Chris

In some of the columns in the calculation there may be an empty cell with no data in it. i.e. K8:K12="A001" but K10 is empty
The result is correct but there is a small "i"appears in the corner of the cell stating the formula in this cell relates to cells that are currently empty.
Its not a problem and If I put N/A in it will rectify it, but wondered if there was a simple way of ignoring the empty cells.
The formula itself is working brilliantly.

Many thanks again for your time

Upvote 0
Is it just the little green error triangle?

I wouldn't worry too much about this as long as the formula is working. You can just get rid of it by clicking 'Ignore Error' or something?

Last edited:
Upvote 0
Ok thanks Chris. Yes it is the green triangle and it does go when you ignore error.
Many thanks for your help

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back