Matching row numbers

[fionamb83]

Hi All!


I am trying to compare the value in one cell, say A1, with values in a column (B), and if they match, return the value of a cell in another column C that has the same row number as the matching value in B.


Like:

A1 --> John Smith

B12 --> John Smith

C12 --> 99 Maple Drive --> Return C12


Any help you could give me would be great!

Thanks!

 

[Raja Kamarthi]

@ fionamb83


I'm not sure if I got your question in the right way but below is my view


Please input the below formula in cell D1:


=OFFSET(A1,(MATCH(A1,B:B,0))-ROW(A1),2)


Hope this should work for you


Regards,

Raja

 

[fionamb83]

Thanks for that Raja, although I am not sure it's what I am looking for. The user selects the name from a combo box. This is referenced to a cell, for argument's sake A1. I want to check if this is the same as any of the names in column B. If it is, I want to output the corresponding address, which is in column C and will have the same row number as the matching name in column B.

So:


User input from combo box --> A1 = John Smith

Search names in column B --> B20 = John Smith

Corresponding address in column C --> C20 = 99 Maple Drive


I know it seems complicated and I could do it with access but what I need at the moment is something small in one excel workbook.


Thanks!

 

[Jai9]

Hi,


I have the combo box in D13 referenced to cell A12.

The input range for combo box is in A13:A17 and the actual data in B13:C20.


Try,


=INDEX(B13:C20,MATCH(OFFSET(A13,A12-1,0),B13:B20,0),2)


You have to change range references to suit your needs.


Jai

 

[fionamb83]

Thanks! I figured it out. I wasn't fully understanding what was going on with INDEX, OFFSET and MATCH before I saw them working. I had in the end


=INDEX(D2:E85,(MATCH(A2,D2:D85,0)),2)


A2 being the value of the name, the fill for the combo box being in D and the addresses in E.