Excel fomularbar shows correct Value, Sheet shows VALUE error

1

Here is the data I have been using, data format all General:

Name    Address
Adam    250
Mohame  150
Adam    220
Adam    423
Yasir   240
Ibrahim 236
Adam    242

Index formula cell B11 contains the formula to match value in cell A11:

=IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$11,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$11,ROW($A$1:$A$8)),ROW(1:1)),2))

The formula bar shows correct values, but cell B11 shows #VALUE!.

Adam Arsh

Posted 2013-08-29T07:48:15.233

Reputation: 11

you may be able to get an answer on SO instead. – None – 2013-08-29T09:12:20.443

Answers

0

Your formula seems to be working for me. If you aren't using it properly (i.e. by not using Ctrl+Shift+Enter), you should be getting blanks.

One thing you can do is to make the formula a bit shorter by using IFERROR() like this:

=IFERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$11,ROW($A$1:$A$8)),ROW(1:1)),2),"")

Jerry

Posted 2013-08-29T07:48:15.233

Reputation: 4 716

hi thank for the reply, i have use new work sheet but still not working for me, by the way which version of excel is you using is there any thing related to Version issue – Adam Arsh – 2013-09-01T03:21:02.577

@AdamArsh I have Excel 2007. What exactly do you have as values and worksheets? – Jerry – 2013-09-01T08:20:11.467

Hi Jerry: I just create new sheet and entered only the sample values above and the data formate is general. no other data in the sheet.but still not showing the value, i am using Excel 2007 too – Adam Arsh – 2013-09-03T03:37:57.130

@AdamArsh I'm not sure why you're getting the error. See the spreadsheet I made here.

– Jerry – 2013-09-03T05:06:27.187

Hi Jerry just looked at you file, the values are there – Adam Arsh – 2013-09-04T07:50:49.380

Hi Jerry just looked at you file, the values are there. if i edit the formula then the value disappears. for example if i put the rage from B8 to B9, or if change any part and replace the same value it disappear for example i changed A1 to A2 and back to A1 the value disapears in it, but i copy exatlly to other cell it diappears. I wanna change the rage cox my data is from A1 to A10000. any idear – Adam Arsh – 2013-09-04T07:57:16.290

@AdamArsh After you change the formula, do not press Enter. Press Ctrl+Shift+Enter as mentioned in my answer. – Jerry – 2013-09-04T09:08:05.240

Hi Jerry: Ctrl+Shift+Enter works perfectly, Thanks for the reply and i would appreciate if you could tell me more abt why just enter dont works thanks once again – Adam Arsh – 2013-09-05T03:05:28.837

@AdamArsh This is because the formula is an array formula; i.e. it returns more than one value in a single cell and excel cannot display this normally. Pressing Ctrl+Shift+Enter will make it choose one value (either the first result or the only one without error). You can try pressing F9 instead of Enter to see all the different values returned :) – Jerry – 2013-09-05T04:15:03.893

Hi Jerry thank Quick reply and now i understand the reason :) – Adam Arsh – 2013-09-05T04:56:53.817