Formula Help

Killer_Bud

New Member
Joined
Nov 7, 2005
Messages
21
Style W Formula Page1.xls
KLMNOPQRST
100.250.3750.50.6250.750.87511.25
20.2512.853.514.485.457.039.2513.4024.73
30.2523.784.856.217.599.5611.2515.6323.28
4434.806.288.059.8512.1014.2018.9826.03
55.8345.837.709.9112.1314.8618.0622.3130.16
60.37556.889.1511.7314.4117.4021.4825.6534.31
767.9310.6013.5916.7020.1924.4129.0039.85
879.8112.8916.3820.5024.4828.7633.4645.40
9811.4314.9018.8522.9427.8132.8138.4852.35
10913.5617.4521.9326.5832.0637.6543.7559.29
111016.0020.2825.2330.5036.6343.2050.2367.64
Sheet1



Can someone please help me.
In cell K5 I'd like a formula to go:

=IF(K6>K2,VLOOKUP(K4,L1:T52,MATCH(K6,L1:T1,0),0)),IF(K6="",MATCH(K3,L1:T1,0))

I keep getting an error and I can't tell why. Any ideas ?

Thanks,
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Misplaced closing parenthesis:

=IF(K6>K2,VLOOKUP(K4,L1:T52,MATCH(K6,L1:T1,0),0),IF(K6="",MATCH(K3,L1:T1,0)))
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Worked for me...
Book1
KLMNOPQRST
100.250.3750.50.6250.750.87511.25
20.2512.853.514.485.457.039.2513.424.73
30.2523.784.856.217.599.5611.2515.6323.28
4434.86.288.059.8512.114.218.9826.03
57.745.837.79.9112.1314.8618.0622.3130.16
60.37556.889.1511.7314.4117.421.4825.6534.31
767.9310.613.5916.720.1924.412939.85
879.8112.8916.3820.524.4828.7633.4645.4
9811.4314.918.8522.9427.8132.8138.4852.35
10913.5617.4521.9326.5832.0637.6543.7559.29
11101620.2825.2330.536.6343.250.2367.64
Sheet1
 

Killer_Bud

New Member
Joined
Nov 7, 2005
Messages
21

ADVERTISEMENT

seems to work when K6>K2, but when K6="", I get an #N/A ?
This is what I need to correct. When K6="", I need it to match K3
to L1:T1
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,808
Office Version
  1. 365
Platform
  1. Windows
Still works for me...
Book2
KLMNOPQRST
100.250.3750.50.6250.750.87511.25
20.2512.853.514.485.457.039.2513.424.73
30.2523.784.856.217.599.5611.2515.6323.28
4434.86.288.059.8512.114.218.9826.03
5245.837.79.9112.1314.8618.0622.3130.16
656.889.1511.7314.4117.421.4825.6534.31
767.9310.613.5916.720.1924.412939.85
879.8112.8916.3820.524.4828.7633.4645.4
9811.4314.918.8522.9427.8132.8138.4852.35
10913.5617.4521.9326.5832.0637.6543.7559.29
11101620.2825.2330.536.6343.250.2367.64
Sheet1
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Null is greater than any number in the sort order, so your first test is still true:

IF(K6="",MATCH(K3,L1:T1,0),IF(K6>K2,VLOOKUP(K4,L1:T52,MATCH(K6,L1:T1,0),0)))
 

Killer_Bud

New Member
Joined
Nov 7, 2005
Messages
21
I need my formula to perform this function:

=IF(K6="",VLOOKUP(K4,L1:T52,0,MATCH(K3,L1:T1,0),IF(K6>K2,VLOOKUP(K4,L1:T52,MATCH(K6,L1:T1,0),0)))

Unfortunately this is too many arguments for this to work.
Is there a way to get it to do this?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Shouldn't it be?

=IF(K6="",VLOOKUP(K4,L1:T52,MATCH(K3,L1:T1,0),0),IF(K6>K2,VLOOKUP(K4,L1:T52,MATCH(K6,L1:T1,0),0)))
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top