[Closed] Excel Help Please? =(IF(AND Query

Bracket after $J$3 is in the wrong place...

=IF(test,value if true, value if false)

Edit: Also - not sure you can nest that many if statements.

My eyes!

I'm far from an Excel expert but there surely must be a more elegant way of coding, erm, whatever the hell it is that you're trying to achieve. Maybe a loop or something?

I'm thinking VLOOKUP may be easier, basically Im creating a spreadsheet to list the menu choice of guests, and return the cost of the choice from the data range, something like this...

Still not working

=IF(AND(I16=$I$3, $J$3), IF(AND(I16=$I$4, $J$4), IF(AND(I16=$I$5, $J$5), IF(AND(I16=$I$6, $J$6), IF(AND(I16=$I$7, $J$7), IF(AND(I16=$I$8, $J$8), IF(AND(I16=$I$9, $J$9), IF(AND(I16=$I$10, $J$10), IF(AND(I16=$I$11, $J11), IF(AND(I16=$I$12, $J$12), IF(AND(I16=$I$13, $J$13), "0")))))))))))

Use the formula wizard...

But yes, vlookup will be much easier...

I have a feeling you don't actually know what the AND part does. I presume what you want is for the formula to return the value in J3 if the entry in I16 is equal to that in I3. You are asking it to evaluate if (1) I16=I3 is true AND (2) that J3 is also true (and so on through the formula)

In which case the formula should be =if(I16=I3,J3,something else). Add dollar signs as appropriate.

But even then, vlookup is probably better.

Try =sumif(I3:I13,I16,J3:J16)
RM.

Referring to your second version of the formula: by nesting all the IFs in the TRUE part of the first IF you are essentially saying ALL the conditions have to be true to return a 0.

In other words:
=IF(AND(I16=$I$3, $J$3,I16=$I$4, $J$4,I16=$I$5, $J$5,I16=$I$6, $J$6,I16=$I$7, $J$7, I16=$I$8, $J$8, I16=$I$9, $J$9, I16=$I$10, $J$10, I16=$I$11, $J11, I16=$I$12, $J$12, I16=$I$13, $J$13),“0”,"")

If this is what your intention is then my version is simpler.

If this isn't your intention then tell us what it is. AFAIK you can only nest 7 IFs but I've not checked this myself for a while. If this is the case then we can use an OR function too, to get round this.

Anyone got any tips on how to set this up with VLOOKUP? I have the menu options hidden and then accessed in cells using a list and Data Validation, the cost option is in the next column along, and I want it to place the price for each item in a separate cell so I can have a running total for that column. But. its not clear to me what the 'Lookup_value', 'Table_array', 'Col_index_num' and 'Range_lookup' should be?

=vlookup (

Lookup value - the value you're looking for,

Table_array - the block of cells where the data is with the values you're matching in the first column and extending as far as the cells with the prices in,

Col_index_num - the number of the column with the values in relative to the column with the values in (starting from 1) so if your list is just 'menu item' in one column and price in the next column it'd be 2,

Range_lookup -  always FALSE if you actually want to match a value rather than have Excels wierd fuzzy matching give you a random answer

)

So if you've got your menu items listed in column A and the prices in column b,  then in column C you have someone's menu choice and want column D to be the price of taht choice

You'd set D1 = VLOOKUP ($C1, $A:$B, 2, FALSE)

But as RM says SUMIF should do the job just fine in this instance.

D1 = SUMIF ($A:$A, $C1, $B:$B)

The only reason for using VLOOKUP is that you could do slightly better error flagging if someone could enter a menu option that wasn't listed - the SUMIF would just return 0 whereas VLOOKUP would throw a #N/A which you could trap by wrapping the VLOOKUP in an IFNA so

D1 = IFNA(VLOOKUP(...),"Invalid choice")

Cant see why you are using and at all, makes no sense for the same cell.

Just use your last posted formula with all AND( removed.

Assuming you want to longhand it for now...

your AND formula is not right. the two sections allow for two different expressions so must be written out in full

AND ( x= 1, y = 2)
for this to work x must = 1 and y =2

so for your case
"AND(I16=$I$3),$J$3)"

All this is doing is asking if I16 = I3. the J3 bit is outside the bracket.

even if you bring it inside
AND(I16=$I$3,$J$3)
it still won't work as J3 is not being asked

This is what you're looking for
AND(I16=$I$3,I16 = $J$3)
Both expressions must be run as independent formulas

now to try and find a more elegant way....

Think I've worked out what youre' trying to do.
FOr row 16 you want to work out/lookup the cost from the tbale above.
So in J16 put the following
=vlookup(I16,$I$3:$J$13,2,FALSE)

So lookup whats in I16, go lookup the rows in I3:J13 and when you've found it, give me the value in the 2nd column oh and the false means i'll only accept exactly the right text not the first one that looks about right.

Click and Drag

Hope that helps

The obvious answer is its most expensive option per head, regardless of what's been ordered or total bill split x ways.

The actual difference between cheapest option and equal split bill or most expensive option is never, ever, ever more than the value of someone's time to split it out. Add to that someone will always short their bill because they worked it out differently since they didn't include service/sides/water on the table what ever and its more messy than your original formula.

But yeah vlookup it you must.

Member
Think I’ve worked out what youre’ trying to do.
FOr row 16 you want to work out/lookup the cost from the tbale above.
So in J16 put the following
=vlookup(I16,$I$3:$J$13,2,FALSE)

So lookup whats in I16, go lookup the rows in I3:J13 and when you’ve found it, give me the value in the 2nd column oh and the false means i’ll only accept exactly the right text not the first one that looks about right.

Click and Drag

Hope that helps

BEAUTIFUL, JUST BEAUTIFUL 🙂

you're welcome.
First time I was the first to post the right answer on STW!
on anything.
Ever.