INDEX MATCH Not returning #N/A error

B

buer4499

New Member
Joined
Jun 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,

I can't figure out why I am receiving this error, I want to index and match based of either my data valuation or the concatenated version.

Results table

DATA.xlsx
BCDEFGHI
206/09/2021<- Data validation drop down
3UK<- Data validation drop down
4English<- Data validation drop down
5
6
7UKEnglish44445UKEnglish44446UKEnglish44447UKEnglish44448UKEnglish44449UKEnglish44450UKEnglish44451
8MonTueWedThuFriSatSun
906/09/202107/09/202108/09/202109/09/202110/09/202111/09/2021########
1000:00#N/A
1101:00
1202:00
1303:00
1404:00
1505:00
1606:00
1707:00
1808:00
1909:00
2010:00
2111:00
2212:00
2313:00
2414:00
2515:00
2616:00
2717:00
2818:00
2919:00
3020:00
3121:00
3222:00
3323:00
DASH
Cell Formulas
RangeFormula
C7:I7C7=CONCAT($B$3,$B$4,C9)
D9:I9D9=C9+1
C9C9=B2
C10C10=INDEX(Sheet1!H:H,MATCH(1*(Sheet1!A:A=C7)*(Sheet1!G:G=B10),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
B2List=VALIDATION!$A$1:$A$28
B3List=VALIDATION!$C$1:$C$4
B4List=VALIDATION!$E$1:$E$11



my data range

DATA.xlsx
ABCDEFGH
1CONCATLocationLanguageLockActualDateIntervalInterval %
122UKEnglish44445UKEnglish68.4488659.6833306/09/202100:000.87
123UKEnglish44445UKEnglish58.5459563.5666706/09/202101:001.09
124UKEnglish44445UKEnglish49.3475554.1333306/09/202102:001.10
125UKEnglish44445UKEnglish45.0739263.6166706/09/202103:001.41
126UKEnglish44445UKEnglish41.9521561.2333306/09/202104:001.46
127UKEnglish44445UKEnglish50.775151.1333306/09/202105:001.01
128UKEnglish44445UKEnglish78.3274691.2166706/09/202106:001.16
129UKEnglish44445UKEnglish114.5167121.633306/09/202107:001.06
130UKEnglish44445UKEnglish147.159156.266706/09/202108:001.06
131UKEnglish44445UKEnglish185.1958184.516706/09/202109:001.00
132UKEnglish44445UKEnglish206.3495196.583306/09/202110:000.95
133UKEnglish44445UKEnglish205.4149212.416706/09/202111:001.03
134UKEnglish44445UKEnglish206.6805205.133306/09/202112:000.99
135UKEnglish44445UKEnglish215.5678218.566706/09/202113:001.01
136UKEnglish44445UKEnglish218.2568237.616706/09/202114:001.09
137UKEnglish44445UKEnglish225.6341245.2506/09/202115:001.09
138UKEnglish44445UKEnglish227.2672256.866706/09/202116:001.13
139UKEnglish44445UKEnglish209.9705228.0506/09/202117:001.09
140UKEnglish44445UKEnglish197.7419189.733306/09/202118:000.96
141UKEnglish44445UKEnglish172.2393179.866706/09/202119:001.04
142UKEnglish44445UKEnglish159.291145.433306/09/202120:000.91
143UKEnglish44445UKEnglish138.3583136.2506/09/202121:000.98
144UKEnglish44445UKEnglish128.1515124.3506/09/202122:000.97
145UKEnglish44445UKEnglish94.2042298.2166706/09/202123:001.04
146UKEnglish44446UKEnglish61.6522284.7333307/09/202100:001.37
147UKEnglish44446UKEnglish52.7326251.4507/09/202101:000.98
148UKEnglish44446UKEnglish44.4475750.3507/09/202102:001.13
149UKEnglish44446UKEnglish40.598361.207/09/202103:001.51
150UKEnglish44446UKEnglish37.786548.907/09/202104:001.29
151UKEnglish44446UKEnglish45.7333863.6833307/09/202105:001.39
152UKEnglish44446UKEnglish74.1360390.5166707/09/202106:001.22
153UKEnglish44446UKEnglish106.3691126.766707/09/202107:001.19
154UKEnglish44446UKEnglish134.5131156.0507/09/202108:001.16
155UKEnglish44446UKEnglish172.6784184.283307/09/202109:001.07
156UKEnglish44446UKEnglish182.5414180.466707/09/202110:000.99
157UKEnglish44446UKEnglish189.121194.083307/09/202111:001.03
158UKEnglish44446UKEnglish189.7231191.0507/09/202112:001.01
Sheet1
Cell Formulas
RangeFormula
A122:A158A122=CONCATENATE(B122,C122,F122)


Any help would be apprecaited.

Thanks,
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Try it like
Excel Formula: 
=INDEX(Sheet1!H:H,MATCH(1,(Sheet1!A:A=C7)*(Sheet1!G:G=B10),0))
Also with xl365 you do not need to use Ctrl Shift Enter.
 
Upvote 0
Fluff said:
Try it like
Excel Formula: 
=INDEX(Sheet1!H:H,MATCH(1,(Sheet1!A:A=C7)*(Sheet1!G:G=B10),0))
Also with xl365 you do not need to use Ctrl Shift Enter.
Click to expand...
Hello,

This is still returning an error:

DATA.xlsx
BCDEFGHI
206/09/2021<- Data validation drop down
3UK<- Data validation drop down
4English<- Data validation drop down
5
6
7UKEnglish44445UKEnglish44446UKEnglish44447UKEnglish44448UKEnglish44449UKEnglish44450UKEnglish44451
8MonTueWedThuFriSatSun
906/09/202107/09/202108/09/202109/09/202110/09/202111/09/2021########
1000:00#N/A
1101:00
1202:00
1303:00
1404:00
1505:00
1606:00
1707:00
1808:00
1909:00
2010:00
2111:00
2212:00
2313:00
2414:00
2515:00
2616:00
2717:00
2818:00
2919:00
3020:00
3121:00
3222:00
3323:00
DASH
Cell Formulas
RangeFormula
C7:I7C7=CONCAT($B$3,$B$4,C9)
D9:I9D9=C9+1
C9C9=B2
C10C10=INDEX(Sheet1!H:H,MATCH(1,(Sheet1!A:A=C7)*(Sheet1!G:G=B10),0))
Named Ranges
NameRefers ToCells
Sheet1!_FilterDatabase=Sheet1!$A$1:$H$720C10
Cells with Data Validation
CellAllowCriteria
B2List=VALIDATION!$A$1:$A$28
B3List=VALIDATION!$C$1:$C$4
B4List=VALIDATION!$E$1:$E$11


Thanks,
 
Upvote 0
That's because none of the values in col G sheet1 match the values in col B Dash
 
Upvote 0
They show a match for me? Both intervals of one hour?

1645018505621.png
 
Upvote 0
Change the format of both columns to Numbers
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
VBA to automatically copy paste the specific data from over 10 excel files based off fixed column range (or date) and key figures from various columns
Replies
0
Views
1K
georgejay
G

Forum statistics

Threads
1,214,927
Messages
6,122,309
Members
449,080
Latest member
jmsotelo

Share this page

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