retrieving multiple columns data using vlookup

[jayalaxmi]

Dear all,

I am finding difficulty in retreiving data from multiple columns. I had tried using array formula but I am not ending up with correct results. Pls help and guide me with the same. I have two sheets results and formula.I trying to vlookup email id with results sheet and I want the city column to be updated automatically in the formula sheet according to the J:Q column in the result sheet. Hope its clear. I had manually updated as of now.

Attaching a sample file for reference. Any help is appreciated.

 

[Asheesh]

Hi,

Try the below in E3 and drag it down.

INDIRECT(TEXT(MAX(IF(results!$C$3:$C$218=$D3,IF(results!$J$3:$Q$218<>"",10^5*ROW($J$3:$Q$218)+COLUMN($J$3:$Q$218)+9))),"R0C00000"),)

To be acknowledged with CTRL + SHIFT + ENTER.

Please validate the results since I did not.

 

[jayalaxmi]

Hello and gm


Thank u asheesh for your reply. But the formula doesn't seems to be working at my end.. Kindly guide..I am getting results as blank,#ref, and mobile as output. Whereas it's the state column.

Regards
Jaya

 

[NARAYANK991]

Hi ,

Try the following array formula , to be entered using CTRL SHIFT ENTER :

=INDIRECT("'results'!" & "R" & SUBSTITUTE(TEXT(MIN(IF(results!$C$3:$C$218=$D3,IF(results!$J$3:$Q$218<>"",ROW($J$3:$Q$218)+0.00001*COLUMN($J$3:$Q$218)))),"000.00000"),".","C"),FALSE)

This will retrieve the values in the City column.

Narayan

 

[jayalaxmi]

Thank you so much Narayan Sir for your kind help. It works!!

Regards
Jaya