If statement with lookup array... any advice?
 

I am trying to automate a spreadsheet. The basic formula is this... Any
suggestions or corrections would be appreciated.
=if(Logical_test, lookup_array, lookup_array)

actual formula

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,'X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))

I have also tried

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),if(b3="Top
Mount",LOOKUP(B56,{3800,5300,6200,8700,13500,17000 ,21000,26000,33000},{"X12B39",'X15B39","X18B39","X 22B39","X24B43","X27B47","X30B55","X33B55","X27B71 "}))"")

Thanks.

If statement with lookup array... any advice?
 

You had a single quotation (in front of X15B39) in the last half of formula
that was screwing things up.

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,"X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanH" wrote:

I am trying to automate a spreadsheet. The basic formula is this... Any
suggestions or corrections would be appreciated.
=if(Logical_test, lookup_array, lookup_array)

actual formula

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,'X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))

I have also tried

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),if(b3="Top
Mount",LOOKUP(B56,{3800,5300,6200,8700,13500,17000 ,21000,26000,33000},{"X12B39",'X15B39","X18B39","X 22B39","X24B43","X27B47","X30B55","X33B55","X27B71 "}))"")

Thanks.

If statement with lookup array... any advice?
 

Thanks... I think I've been stairing at the screen for too long. I had two
other people look at this. I had asked them to verify that all my quots were
there and parenthesis were there but nothing. Good eye.

Thanks again.

"Luke M" wrote:

You had a single quotation (in front of X15B39) in the last half of formula
that was screwing things up.

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,"X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"DanH" wrote:

I am trying to automate a spreadsheet. The basic formula is this... Any
suggestions or corrections would be appreciated.
=if(Logical_test, lookup_array, lookup_array)

actual formula

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,'X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))

I have also tried

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),if(b3="Top
Mount",LOOKUP(B56,{3800,5300,6200,8700,13500,17000 ,21000,26000,33000},{"X12B39",'X15B39","X18B39","X 22B39","X24B43","X27B47","X30B55","X33B55","X27B71 "}))"")

Thanks.

If statement with lookup array... any advice?
 

You could greatly reduce the length of your formula if you were to setup a
lookup table.

With this data in the range D1:E11 -

2050.128
3250.158
5150.188
8500.228
1150.248
15400.278
18250.308
22500.338
26000.368
31000.408
41000.408

With this data in the range G1:H9 -

3800.X12B39
5300.X15B39
6200.X18B39
8700.X22B39
13500.X24B43
17000.X27B47
21000.X30B55
26000.X33B55
33000.X27B71

Then your formula becomes:

=VLOOKUP(B56,IF(B3="Back Mount",D1:E11,G1:H9),2)

--
Biff
Microsoft Excel MVP


"DanH" wrote in message
...
I am trying to automate a spreadsheet. The basic formula is this... Any
suggestions or corrections would be appreciated.
=if(Logical_test, lookup_array, lookup_array)

actual formula

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),LOOKUP(B56,{3800,5300, 6200,8700,13500,17000,21000,26000,33000},{"X12B39" ,'X15B39","X18B39","X22B39","X24B43","X27B47","X30 B55","X33B55","X27B71"}))

I have also tried

=IF(B3="Back
Mount",LOOKUP(B56,{2050,3250,5150,8500,1150,15400, 18250,22500,26000,31000,41000},{128,158,188,228,24 8,278,308,338,368,408,408}),if(b3="Top
Mount",LOOKUP(B56,{3800,5300,6200,8700,13500,17000 ,21000,26000,33000},{"X12B39",'X15B39","X18B39","X 22B39","X24B43","X27B47","X30B55","X33B55","X27B71 "}))"")

Thanks.