Nested IF statements with a range of cells

**rteeling35** · 01-08-2013, 01:35 PM

Hello,

I am trying to write an embedded IF statement that has 3 conditions but multiple acceptable options in each condition (does that make sense)? The below is an example of my formula (which works, but is cumbersome). I would like to replace all of the 'OR' statements with one formula which references a table on the 'Lookup Table' tab, rather than having to list out each cell individually. Would it be possible to do a VLOOKUP here?

=IF(AND(OR($N6='Lookup Tables'!$G$4,$N6='Lookup Tables'!$G$6),OR($H6='Lookup Tables'!$H$4,$H6='Lookup Tables'!$H$6,$H6='Lookup Tables'!$H$7),$D6='Lookup Tables'!$I$5),"X","")

Basically, what I want to say is: "If the value of N6 is in this range of cells (G4:G6), and the value of H6 is in this range of cells (H4:H6), and D6 equals Lookup Tables cell I5, then put an 'X', otherwise leave blank...

Any ideas?

Thanks in advance!!

**FDibbins** · 01-08-2013, 01:51 PM

Hi rteeling and welcome to the forum

Give this a try. untested, because I dont have any of your data. if it doesnt work, perhaps it would be easier to help you if you uploaded a sample workbook, showing what data you are working with, a few examples of what your expected outcome would be, and how you would arrive at that (remove any confidential info if necessary).

=iferror(and(vlookup(N6,G4:G6,1,false),vlookup(h6,h4:h6,1,false),$D6='Lookup Tables'!$I$5),"X")

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**rteeling35** · 01-08-2013, 05:24 PM

Hi,

Thanks for your help, however I could not get this forumla to work. Also, I believe the logic applied here will have Excel do the opposite of what I need. I need it to put an "X" when it meets the criteria, not when there is an error (when it doesn't).

I have attached an example of what I need the forumla to do. Any ideas?

Thanks again for you help!

**daddylonglegs** · 01-08-2013, 05:44 PM

Try using MATCH to match against a range, i.e. this formula in row 2 copied down

=IF(AND(ISNUMBER(MATCH($A3,'Lookup Table 1'!B$2:B$3,0)*MATCH($B3,'Lookup Table 1'!C$2:C$3,0)),$C3='Lookup Table 1'!$D$2,$D3&""='Lookup Table 1'!$E$2),"X","")