Using If, Or, or something like that.

**Leah** · 10-31-2006, 10:36 AM

How do I write this function?

Sheet 1, A7 is a drop down list of machines. The list is in Sheet 2, Collumn A.

Sheet 3, Collumn A is a list of options, collumn B is the prices of those options.

Depending on the machine selected in Sheet 1 A7, the options on Sheet 3 have some different prices. There are about 4 different prices, so a group of several machines has the same option price. I need the correct price to show up in the B collumn. I'm listing the possible prices in collumns C, D, and E.

I was trying to use IF and OR, but wasn't doing it correctly. In english, it should look like this:

If Sheet 1, A7 equals Sheet 2, A2 through A8, then Sheet 3, B3 equals Sheet 3, C3. If Sheet 1, A7 equals Sheet 2, A9 through A16, then Sheet 3, B3 equals Sheet 3, D3. Something like that.

**Dav** · 10-31-2006, 10:57 AM

well firstly go to sheet2 and select the range for the dropdown list, then choose insert, name, define. And give it a name of your choice

Go to sheet1 A7 choose data validation, then list and put =the name you chose in the above step

Then you need an if statement if there are only 4 options

something like

=IF(ISERROR(MATCH(Sheet1!A7,Sheet2!A2:A30,0)),"",IF(MATCH(Sheet1!A7:Sheet2!A2:A8,0),Sheet3!B3,IF(MATCH(Sheet1!A7:Sheet2!A9:A16,0),Sheet3!C3,IF(MATCH(Sheet1!A7:Sheet2!A17:A20,0),Sheet3!D3,Sheet3!E3))))

Regards

Dav

**Leah** · 10-31-2006, 12:50 PM

Why the ISERROR? It's not working for me so far, it's returning an N/A# at some point.

=IF(Proposal!A7=Specs!A2,C2,IF(MATCH(Proposal!A7,Specs!A3:A17,0),D2,IF(MATCH(Proposal!A7,Specs!A18:A30,0),E2,IF(MATCH(Proposal!A7,Specs!A33:A37,0),F2,N/A))))

That's the code I have posted. Where I want it to return E2 or F2, it's returning the N/A# error (not the N/A I have listed for a false answer). The lookup array is not and cannot be sorted in ascending or descending order, it is a mix of text and numbers, but it has to be in the order I originally wrote it.

**Dav** · 10-31-2006, 01:00 PM

Assuming you have dealt with the spaces that appear in the forum in the posts

iserror appears first to deal with the value not being a match in the range a2:a30 which would cause an error, in this case it returns a blank ""

I have spotted a typo so try the below some , where typed as :

=IF(ISERROR(MATCH(Sheet1!A7,Sheet2!A2:A30,0)),"",I F(MATCH(Sheet1!A7,Sheet2!A2:A8,0),Sheet3!B3,IF(MATCH(Sheet1!A7,Sheet2!A9:A16,0),Sheet3!C3,IF(MATCH(Sheet1!A7,Sheet2!A17:A20,0),Sheet3!D3,Sheet3!E3))))

Regards

Dav

**Leah** · 10-31-2006, 02:23 PM

I did deal with your typos. I don't need ISERROR because there is always a value. I can't figure out why the code I used, posted above, is only returning a correct value for the first two conditions and an error for the next two. Can you figure out from my code what I might be doing wrong?

**Leah** · 10-31-2006, 02:43 PM

I've isolated the problem. It is only allowing the first false value to be possible. After that, it goes to #N/A. Am I coding incorrectly?

=IF(Proposal!A7=Specs!A2,C2,IF(MATCH(Proposal!A7,Specs!A3:A17,0),D2,IF(MATCH(Proposal!A7,Specs!A18:A30,0),E2,IF(MATCH(Proposal!A7,Specs!A33:A37,0),F2,N/A))))

**Dav** · 11-01-2006, 04:08 AM

How about

=IF(Proposal!A7=Specs!A2,C2,IF(MATCH(Proposal!A7,S pecs!A3:A17,0)>0,D2,IF(MATCH(Proposal!A7,Specs!A18:A 30,0)>0,E2,IF(MATCH(Proposal!A7,Specs!A33:A37,0)>0,F2, N/A))))

Let me know if it works

Regards

Dav

**Leah** · 11-01-2006, 10:07 AM

No, that didn't work. It's still returning a #N/A error. It will only evaluate the first two arguments. That's what I don't get. An IF string is supposed to take up to 7 arguments.

**Dav** · 11-01-2006, 10:28 AM

Perhaps you need to post the spreadsheet as a zipped file. I did notice that a31:a32 do not appear in any of the ranges.

Alternatively if you have the forumula in the toolbar and select part of it and press F9 it will evaluate itit will return the values, you can do this in bits to see which part of the formula is returning errors. if at the end you press escape rather than return, it will not save the changes.

Another thought it will return N/A, as the if staements will return N/A if they do not make a match. try the following

=IF(Proposal!A7=Specs!A2,C2,IF(MATCH(Proposal!A7,S pecs!A3:A37,0)<16,D2,IF(MATCH(Proposal!A7,Specs!A18:A37,0)<14,E2,IF(MATCH(Proposal!A7,Specs!A33:A37,0)>0 ,F2, N/A))))

Regards

Dav

**Leah** · 11-01-2006, 10:55 AM

That's the problem right there. If the formula doesn't find a match, it returns #N/A, making the rest of the function void. I don't want it to return an error, I want it to return a false and move onto the next argument. Can this be done with an OR statement at all, rather than MATCH?

**Leah** · 11-01-2006, 11:03 AM

I'm trying to get creative and use ISNA somehow, but I'm having trouble figuring out exactly where to put the FALSE result. This is what I have so far:

=IF(Proposal!A7=Specs!A2,C2,IF(ISNA(MATCH(Proposal!A7,Specs!A3:A17,0)),IF(MATCH(Proposal!A7,Specs!A18:A30,0),E2,IF(MATCH(Proposal!A7,Specs!A33:A37,0),F2,D2))))

**Leah** · 11-01-2006, 11:32 AM

Okay, thanks for all of your help. I finally wrapped my brain around it and it works now. Here's what I came up with:

=IF(Proposal!A7=Specs!A2,C2,IF(NOT(ISNA(MATCH(Proposal!A7,Specs!A3:A17,0))),D2,IF(NOT(ISNA(MATCH(Proposal!A7,Specs!A18:A30,0))),E2,IF(NOT(ISNA(MATCH(Proposal!A7,Specs!A33:A37,0))),F2,"N/A"))))