Need Suggestion on Correcting Value Not Available Error

CARBOB

Well-known Member
Joined
Jun 6, 2005
Messages
1,870
The formula in column L searches for the value in column K and returns the value in column A on another worksheet that matches the value in column K, which should be 1. I am getting an Value Not Available Error. Any ideas on correcting the problem?
Excel Workbook
KL
36013###
37118###
38477###
39179###
40789###
DIGIT REPEATS
Excel 2007
Cell Formulas
RangeFormula
K36=SMALL(B36:D36,1)&SMALL(B36:D36,2)&SMALL(B36:D36,3)
K37=SMALL(B37:D37,1)&SMALL(B37:D37,2)&SMALL(B37:D37,3)
K38=SMALL(B38:D38,1)&SMALL(B38:D38,2)&SMALL(B38:D38,3)
K39=SMALL(B39:D39,1)&SMALL(B39:D39,2)&SMALL(B39:D39,3)
K40=SMALL(B40:D40,1)&SMALL(B40:D40,2)&SMALL(B40:D40,3)
L36=INDEX('MH-ML'!$A$6:$A$24,MATCH($K36,'MH-ML'!$B$6:$AE$24,0))
L37=INDEX('MH-ML'!$A$6:$A$24,MATCH($K37,'MH-ML'!$B$6:$AE$24,0))
L38=INDEX('MH-ML'!$A$6:$A$24,MATCH($K38,'MH-ML'!$B$6:$AE$24,0))
L39=INDEX('MH-ML'!$A$6:$A$24,MATCH($K39,'MH-ML'!$B$6:$AE$24,0))
L40=INDEX('MH-ML'!$A$6:$A$24,MATCH($K40,'MH-ML'!$B$6:$AE$24,0))
Excel Workbook
ABCDE
14-1011023035047
150000012024036
161001013025037
MH-ML
Excel 2007
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Match only works if you're looking up one row or one column. You cannot look up a value in multiple rows/columns in the same match argument.
 
Upvote 0
Match only works if you're looking up one row or one column. You cannot look up a value in multiple rows/columns in the same match argument.

any suggestions for a formula that would work, using multiple columns?
 
Upvote 0

Forum statistics

Threads
1,222,310
Messages
6,165,259
Members
451,949
Latest member
bovacik

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
Back
Top