• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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
 
Top