# Single item lookup = Index - Match on multiple match array

#### sampal

##### New Member
Hi

I am trying to retrieve corresponding value from a table;

Table as under

411659 \$129.00 \$193.00 \$187.00 \$136.00 \$180.00 \$108.00

407345 \$154.00 \$178.00 \$118.00 \$189.00 \$184.00 \$130.00

419885 \$105.00 \$116.00 \$115.00 \$116.00 \$128.00 \$136.00

414030 \$102.00 \$199.00 \$156.00 \$139.00 \$170.00 \$103.00

416372 \$191.00 \$123.00 \$166.00 \$141.00 \$114.00 \$137.00

410398 \$102.00 \$178.00 \$147.00 \$109.00 \$191.00 \$100.00

414336 \$162.00 \$110.00 \$170.00 \$179.00 \$161.00 \$164.00

417560 \$170.00 \$160.00 \$179.00 \$180.00 \$162.00 \$115.00

404455 \$198.00 \$105.00 \$157.00 \$115.00 \$148.00 \$121.00

If i type in cell A1 , 109 then in A2 i need a formula that looks up to give me the corresponding account number from the table which is 410398. This formula should be able to look-up any amounts from the table. So should i type in 121 in cell A1, then i should see account number 404455 in cell A2...

Any ideas?

#### Smallman

##### Excel Ninja
Hi Sampal

There must be an easier method than this. I use (a) as the named range of your values. And Range A2:A4 as your Accounts.

=INDEX(\$A\$2:\$A\$5, SMALL(IF(a=\$A\$1, ROW(a)-MIN(ROW(a))+1, ""), ROW(a)-MIN(ROW(a))+1), COLUMN(a)-MIN(COLUMN(a))+1)

Array entered Ctrl Shift Enter.

Take care

Smallman

#### sampal

##### New Member
Yes, this works however, I am not able to comprehend how this works. are you able to break this down and explain,

thanks

#### Smallman

##### Excel Ninja
Sampal

The Column that you want to lookup Col A, will always be 1. Something I missed the first time round, so this improvement may help your cause.

=INDEX(\$A\$3:\$A\$6, SMALL(IF(a=\$A\$2, ROW(a)-MIN(ROW(a))+1, ""), ROW(a)-MIN(ROW(a))+1), 1)

Sing out if you need further assistance breaking it down after you obsorb this.

Take care

Smallman

#### sampal

##### New Member
Thanks,

I understand that you need a row and column number for the index function to work.

However I am still not able to understand how this bit in the formula works SMALL(IF(a=\$A\$2, ROW(a)-MIN(ROW(a))+1, ""), ROW(a)-MIN(ROW(a))+1), 1)

could you please break down the small and the nested if functions, here..

#### jeffreyweir

##### Active Member
OR you could array enter this:

=INDEX(A:A,MAX(ROW(d)*(d=l)))

...where d is a named range that covers the data, and l is a named range where the lookup item is.

#### Smallman

##### Excel Ninja
Hi Jeff

That is a very nice effort! I am clapping. Good work!!!

Was trying to post this as an update but a moot point now.

=INDEX(\$A\$3:\$A\$6,SMALL(IF(a=\$A\$2,ROW(a)-MIN(ROW(a))+1, ""),1),1)

Sampal

The Small part returns the relative position of the match with what is in A2. It is picking up the Row Position. You could Also use Large in the following way. Produces the same result.

=INDEX(\$A\$3:\$A\$6,LARGE(IF(a=\$A\$2,ROW(a)-MIN(ROW(a))+1, ""),1),1)

It just isolates the ROW part of the Index. Actually the above is the same as writing;

=INDEX(\$A\$3:\$A\$6,3,1)

Where the data is held in the 3rd Row and 1st Col

Do some further research on Small and Large functions and here is a good place to start.

http://www.myonlinetraininghub.com/excel-min-max-small-and-large-functions

Take care

Smallman

#### sampal

##### New Member
OR you could array enter this:

=INDEX(A:A,MAX(ROW(d)*(d=l)))

...where d is a named range that covers the data, and l is a named range where the lookup item is.

Thanks for this, however could you explain why it only works on a full Index column and not a named range or a specific range. e.g. it works when you have =INDEX(A:A,MAX(ROW(d)*(d=l))) and not when = INDEX(range1,MAX(ROW(d)*(d=l)))

I get #ref error

any ideas

#### sampal

##### New Member
Any idea why i have to specify whole coloumn in index as oppose to a specific selected range ? I have not heard any one respond. appreciate if anyone can answer. thanks