# Need Suggestion on Correcting Value Not Available Error

#### CARBOB

##### Well-known Member
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### bschwartz

##### Well-known Member
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.

#### CARBOB

##### Well-known Member
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?

#### CARBOB

##### Well-known Member
I redid the worksheet and entered the data into one column. Works now.

Replies
1
Views
200
Replies
1
Views
217
Replies
3
Views
198
Replies
1
Views
210
Replies
2
Views
251

1,195,948
Messages
6,012,475
Members
441,701
Latest member
vnkendijs

### 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?

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