Quick Links ▼

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » training excel vba - INDEX, MATCH AND LARGE FORMULA

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

training excel vba - INDEX, MATCH AND LARGE FORMULA

Resolved · Low Priority · Version Standard

Reply Tue 24 Apr 2007, 12:18Delegate Yanti said...

Yanti has attended:
Excel Advanced course

INDEX, MATCH AND LARGE FORMULA

HI

I am working on a sheet with 2 columns and organizing these in a separate sheet.

firstly I am organizing in ASCENDING ORDER a long list with the following:

=LARGE(IF(('List clients'!$F$3:$F$482>" "),'List clients'!$K$3:$K$482),K)

secondly I am retreiving the data of the nearby columns in order to match the ascending values with the following:

=INDEX('List clients'!$A$3:$A$482,MATCH('NAME OF SHEET WHERE THE LIST IS'!$PK,'List clients'!$K$3:$K$482,0),1)

The problem I have is that I have a few amounts in the ascending column that perfectly match. When that happens the Index and Match formula (second above) cannot retreive the correct names but retreive the first name in th elist, twice.

I will try to explain better:

If the original file is as below,

column A Column B

CLIENT X 558
CLIENT A 555
CLIENT B 555
CLIENT 5 560

I apply the first formula above to organize the column B, and the second formula to retreive the information in column A accordingly.

unfortunately instead of getting the following result:

CLIENT Y 560
CLIENT X 558
CLIENT A 555
CLIENT B 555

I obtain the below, which is not correct:

CLIENT Y 560
CLIENT X 558
CLIENT A 555
CLIENT A 555

in other words CLIENT A is repeated twice and CLIENT B ( with same reference number, 555) does not appear at all.

Is there a way to solve this matter?

Kind Regards
yanti

For upcoming training course dates see: Pricing & availability

Reply Fri 4 May 2007, 14:05Trainer Garry said...

RE: INDEX, MATCH AND LARGE FORMULA

Have you tried using a vlookup instead of Index and Match

Reply Fri 4 May 2007, 15:11Delegate Yanti said...

RE: INDEX, MATCH AND LARGE FORMULA

no it would not work.
Vlookup looks for a value in the leftmost column of a table, and returns a value in the same row from the column I specify.

2 problems:

first: the value to look for is to the right of teh column I specify.
second: the table is not in ascending order.

Any other suggestion?

Reply Fri 4 May 2007, 15:21Delegate Yanti said...

RE: INDEX, MATCH AND LARGE FORMULA

I just tried adding a column and it still does not work because vlookup also retreive the first value twice.

Reply Tue 19 Jun 2007, 14:04Trainer Rich said...

RE: INDEX, MATCH AND LARGE FORMULA

Yanti,

Sorry it's taken a while to reply. This is one of the most complicated questions we've had on this forum, and it's more of a consultancy job.

I will try to assist you through the forum now.

I'm not sure your approach is correct. Could you please explain exactly what you're trying to do, without resulting to formulas? But perhaps a few columns and 'before and after' results would help.

Once I can understand what you're trying to achieve, I will be able to assist you further.

You say you are trying to organise a column using LARGE(). The LARGE() function returns the k-th largest value in a data set, so I'm not sure it should be used to organise a column. Also in your example, you have specified k as simply 'K' whereas that parameter should be a number.

Regards, Rich

Reply Sat 8 Aug 2009, 12:25Delegate Jo said...

RE: INDEX, MATCH AND LARGE FORMULA

good morning,
i'am having the same problem that Yanti has.

i've to rank an array by the biggest value and i've to bring also the name of that column (for ex: if we have a most sell cars). For example we have:

ford 100
fiat 200
nissan 400
opel 100

what i have using the large function is:

nissan 400
fiat 200
ford 100
ford 100

So when we have equals values they don't bring the right name. Do you compreend ?

thanks for the help
Jo

Excel articles

· How Many Different Ways Can You Sum In Excel 2007?
· Microsoft Excel 2007
· Excel: From The Basic To The Advanced
· How Sharing Your Excel Data Can Help Your Business Flourish
· Special Characters in Excel

Excel tip:

Create your own custom list on Excel 2010!

If you know how to use the auto-fill option on Excel then why not create your own customs lists?

The auto fill feature saves you time by allowing you to enter one of the list entries into a cell and then use your mouse to automatically drag the rest of the list into the cells below, above or to either side of the initial cell. When using your mouse to perform this task you will see a thin black cross appear at the bottom right hand side of the cell. Click, hold and drag to make the list appear.

Default lists include weekdays and months. To create your own list in Excel 2010 do the following;

>File
>Options
>Advanced
>Scroll right to the bottom of the page and you will see a buttom "edit custom lists", click this button
>enter your list in the list entries
>click add

Now try it out. Good luck.
>

View all Excel hints and tips