Formula for checking a cell and determining data set based on cell value

First base formula:
=IF(D2="N", LOOKUP(G5, {0,351,601,1101,2101,3101,5101,10101}, {250,500,1000,2000,3000,5000,10000,"ERROR"})

Second base formula:
=IF(D2="Y", LOOKUP(G5, {0,601,1101,2101,3101,5101,10101,20101,40101}, {500,1000,2000,3000,5000,10000,20000,40000,"ERROR"}))


Merged into one long formula:

=IF(D2="N", LOOKUP(G5, {0,351,601,1101,2101,3101,5101,10101}, {250,500,1000,2000,3000,5000,10000,"ERROR"}), IF(D2="Y", LOOKUP(G5, {0,601,1101,2101,3101,5101,10101,20101,40101}, {500,1000,2000,3000,5000,10000,20000,40000,"ERROR"}), ""))

Create 2 tables, 1 for Y and the other for N...

The table for N would look like this:

0......350......250
351...600......500
601...1100....1000
1101..2100....2000
2101..3000....3000
3101..5100....5000
5101..10100...10000

Assume that table is in the range I2:K8

The table for Y would look like this:

0.........600.........500
601.....1100........1000
1101....2100.......2000
2101....3100.......3000
3101....5100.......5000
5101....10100.....10000
10101...20100....20000
20101...40100....40000

Assume that table is in the range I11:K18

Then, this formula entered in G8:

=IF(AND(D2="N",ISNUMBER(G5)),IF(G5>10100,"Error",LOOKUP(G5,I2:K8)),IF(AND(D2="Y",ISNUMBER(G5)),IF(G5>40100,"Error",LOOKUP(G5,I11:K18)),""))

I like Tony's idea of an easily editable lookup table, but since your tables would be so similar, I believe a single table will work and allow an INDEX/MATCH to work.

\1

Please Login or Register  to view this content.

This shows a single table M1:O11, which then allows this formula to do the job:

=INDEX($N$2:$O$11, MATCH(G5, $N$2, 1), MATCH(D2, $N$1:$O$1, 0))

Wow, first response in 33 minutes! You guys are awesome. I won't need to be editing the values, so JBeaucaire's first response will work for me. I tested it in my table(edit: should've said spreadsheet) and it works flawlessly. If I ever need to edit the values, I'll look into implementing Tony's solution. I owe you guys both a beer! Thanks again! Marking as solved.

Make mine St. Pauli Girl Dark.

Thanks for the feedback!

Coke Zero and some Cheetos... yeah, that's me.

(stares down at multicolored keyboard...didn't used to be multicolored)

You've already done it!