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

Searching in a table

J

jagmohan

New Member
Hi,


I am not sure how should I do this in Excel.


1. I have a 10x10 table with unique values.

2. I want to search this table for an exact match and get the value in the left(right)most column corresponding to this row. e.g. if the match is found in Column 4, Row 5 then I want value in Column 1 in Row 5.


How do I do this?


regards,


Jagmohan

P.S. If this question is already answered - my apologies. But I also could not think of right way to search it in the forum.
 
Jagmohan,

Firstly welcome to the Chandoo.org Forum


Assuming your data is in A1:J10 and is a named range called Data and your lookup value is in B12

then

=OFFSET(A1,SUMPRODUCT(ROW(Data)*(Data=B12))-1,)

will do what you want


Now if your data isn't in A1:J10 but is still called Data

and lookup value is now in B1

The following mod will do it for you

=OFFSET(INDIRECT(ADDRESS(ROW(Data),COLUMN(Data))),SUMPRODUCT(ROW(Data)*(Data=B1))-ROW(Data),)
 
Hui,


Looking at your reply, i got the idea dat dis could also be executed through Index, Match and so i tried using that. But the formula yields #N/A. The formula i used is-:


INDEX(A2:A10,MATCH(B12,Data))


Is there something I am doing wrong or Index Match could not be used in dis case???


Thanks

Niting
 
Sumproduct acts as an array formula and is able to process each row of the range

Whereas Match is unable to do that
 
You must log in or register to reply here.
Back
Top