CODEWITHSUNDEEP
excelformulalookup
Cignitor
Cignitor

Reputation: 1097

Look up multiple rows

I have an Excel spreadsheet like this:

SO24544684 question example

and wish to access the entire address by buyer's name.

For example, for LELY TANIA WIJAYA I am able to get SUTERA JELITA 2 NO. 29 (the first line of the address) by using VLOOKUP but then I find it difficult to get the second and third rows (RT 002 ... etc).

Is there a formula or other easy way to get the whole address?

Upvotes: 0

Views: 66

Answers (1)

pnuts
pnuts

Reputation: 59495

Switch to the INDEX/MATCH combination. If LELY TANIA WIJAYA is in A1 then MATCH("LELY TANIA WIJAYA",A:A,0) will return 1 (the first row in ColumnA) to feed into the INDEX function: 

=INDEX(B:B,MATCH("LELY TANIA WIJAYA",A:A,0))

to return the contents of the cell in the corresponding row in the B column. For the next two rows: 

=INDEX(B:B,MATCH("LELY TANIA WIJAYA",A:A,0)+1)

and 

=INDEX(B:B,MATCH("LELY TANIA WIJAYA",A:A,0)+2)

Upvotes: 1

Related Questions