Lookup problem

Hello Friends.........How are you?

Friends help me........i am facing a problem in excel.........

i attached a Test file of excel with problem

in the file it contains two i.e. Sheet1 and Sheet2

I create a Searchable Combo box in sheet1.........

In sheet2 contain (3 Column) Account Number, Name of Account Holder and Rupees

In the Account Number (Column) it have 4 accounts number.........but C7 and C9 are the same account number but names are different in "Name of Account Holder" Column.............

My combo box is searchable please enter the account number 1234

Now the problem is in Sheet1 when i insert the account number in Combo box i.e. 1234........it shows 1234 two times (i mean to say that choose 1234 or 1234 above or below) but on account number 1234 it have 2 different name i.e. RAZ and RITA in Sheet2..........

I want when i enter the account number 1234 in combo box.......it gives me two (1234 and 1234) accounts number for choose..........
i want when i choose first 1234 it show the record of RAZ
and when i choose second 1234 it show the record of RITA

This is the problem and how to solve this............

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Your explanation of what you wanted was NOT clear. I moved the combo box as it continually interfered with setting the array formulae. You can move it back... it doesn't seem to be working, anyway, as it's only calling up 1 account number.

In G4, copied across:

=IFERROR(INDEX(Sheet2!$G:$G,SMALL(IF(Sheet2!$C$7:$C$10=$G$3,ROW(Sheet2!$C$7:$C$10)),COLUMNS(Sheet1!$G4:G4))),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

If you do not mind me saying so.. that is the MOST UNHELPFUL answer I have ever had here.

What does "not working as I want" mean? You didn't explain your problem clearly. I made a helpful guess. You need to explain what you DO want. If you want help here, you need to explain what you want. The number of mind-readers here is very small.

with a pivot table.

see the attached file.

Glenn Kennedy,

Thanks for taking pain.......sorry for that..........really sorry

Thanks for taking pain.......sorry for that..........really sorry

In addition to the sorry, I suppose you could solve the problem by posting a helpfull anwer.