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

[Cearle]

Has anyone else had problems with the Match function. Help!?
Total
$ (45.77)
$ (293.95)			Lookup Value	Inverse of Lookup	Match Formula Result	Formula 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>

 

[kevatarvind]

can you pls upload your excel file in any sharing site and paste link here ?

 

[Jonmo1]

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]

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.

 

[Cearle]

ADVERTISEMENT

can you pls upload your excel file in any sharing site and paste link here ?

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]

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]

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

 

[Cearle]

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.