# Excel Match Function returns #N/A even though numeric value exists in list

#### Cearle

##### New Member
Has anyone else had problems with the Match function. Help!?
Total
\$ (45.77)
\$ (293.95) Lookup ValueInverse of LookupMatch Formula ResultFormula in cell
\$ (487.74) \$ 45.77 \$ (45.77)19=MATCH(H21,D:D,0)Finds this one
\$ (574.93) \$ 3,989.78 \$ (3,989.78)#N/A=MATCH(H22,D:D,0)But doesn't find this
\$ (699.22) \$ 2,228.47 \$ (2,228.47)#N/A

<tbody>
</tbody>
\$ (738.22) \$ 1,604.37 \$ (1,604.37)#N/A
\$ (957.28) \$ 2,929.15 \$ (2,929.15)#N/A
\$ (1,079.53) \$ 2,310.74 \$ (2,310.74)#N/A
\$ (1,119.45) \$ 5,327.83 \$ (5,327.83)#N/A
\$ (1,227.36) \$ 2,823.81 \$ (2,823.81)#N/A
\$ (1,300.07) \$ 2,941.52 \$ (2,941.52)#N/A
\$ (1,430.43) \$ 2,891.85 \$ (2,891.85)#N/A
\$ (1,470.82) \$ 6,678.22 \$ (6,678.22)305
\$ (1,497.31) \$ 3,180.06 \$ (3,180.06)#N/A
\$ (1,857.66) \$ 36,951.57 \$ (36,951.57)#N/A
\$ (1,989.93)
\$ (2,012.62)
\$ (2,173.32)
\$ (2,297.79)
\$ (2,242.27)
\$ (3,702.32)
\$ (3,786.17)
\$ (3,818.81)
\$ (3,895.11)
\$ (3,951.43)
\$ (3,935.98)
\$ (3,960.60)
\$ (3,984.40)
\$ (3,989.78)yellow indicates conditional formatting found the value
\$ (4,250.63)
\$ (4,360.88)
\$ (4,402.60)
\$ (4,493.35)
\$ (4,524.05)
\$ (4,539.06)
\$ (4,651.92)
\$ (4,876.74)
\$ (4,889.97)
\$ (5,083.29)
\$ (5,107.90)

<tbody>
</tbody>

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

#### Jonmo1

##### MrExcel MVP
Which value is in H22
\$3,989.78 Or \$(3,989.78)
?
How is the Inverse value obtained? Are you manually typing it in, or is there a formula in h22 ?

Try applying the ROUND function for 2 decimal places in your match formula
=MATCH(ROUND(H22,2),D:D,0)

#### Pcool

##### Board Regular
Might be because it is looking for an exact match (the 0 in the last parameter of the match function)
Edit: I guess I would have to see the real spreadsheet.

Last edited:

#### Cearle

##### New Member

Thanks so much for responding. I'd love to upload it but don't know how to-i'm a novice to forums. Can I email this really small file to you? Or is there a way of uploading on this site?

#### Cearle

##### New Member
Might be because it is looking for an exact match (the 0 in the last parameter of the match function)
Edit: I guess I would have to see the real spreadsheet.

Yes, the exact match is critical b/c they are dollar amounts. I used all of the CELL, and TYPE functions to make sure they match. Vlookup returns NA also for some of the numbers. I'd be happy to send you the file also if you provide your email or how to upload the file here?

#### Cearle

##### New Member
Yes, tried the rounding function and it still doesn't work! arghh

#### Cearle

##### New Member
Which value is in H22
\$3,989.78 Or \$(3,989.78)
?
How is the Inverse value obtained? Are you manually typing it in, or is there a formula in h22 ?

Try applying the ROUND function for 2 decimal places in your match formula
=MATCH(ROUND(H22,2),D:D,0)

The inverse is simply putting a negative sign in front of the reference to the previous column. Nothing is manually typed in.

Replies
3
Views
695
Replies
2
Views
194
Replies
12
Views
924
Replies
1
Views
206
Replies
11
Views
551

1,195,949
Messages
6,012,478
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.

### Which adblocker are you using?

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

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