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!.

1
  • you may be able to get an answer on SO instead.
    – user222864
    Aug 29, 2013 at 9:12

1 Answer 1

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),"")
10
  • 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
    Sep 1, 2013 at 3:21
  • @AdamArsh I have Excel 2007. What exactly do you have as values and worksheets?
    – Jerry
    Sep 1, 2013 at 8:20
  • 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
    Sep 3, 2013 at 3:37
  • @AdamArsh I'm not sure why you're getting the error. See the spreadsheet I made here.
    – Jerry
    Sep 3, 2013 at 5:06
  • Hi Jerry just looked at you file, the values are there
    – Adam Arsh
    Sep 4, 2013 at 7:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .