Forum Discussion

Aminnemati's avatar
Aminnemati
Copper Contributor
Jul 16, 2019
Solved

How to handle table returned from lookupvalue function?

Hi, I have recently been asked a question relating data imported from a csv file into Excel worksheet. I want to know name of the cities with lowest and highest revenue. I did some online research a...
excel
  • Roger Govier's avatar
    Roger Govier
    Jul 16, 2019

    Aminnemati 

     

    Hi

    I inserted two columns after City, then selected column F and did Data > Text to Columns > Comma delimiter to split out State and Country. I then deleted the inserted columns G and H.

     

    I inserted 4 rows at the top of the sheet and in cell F2 entered the formula

    =INDEX(F6:$F$5523,MATCH(MAX(E6:E5523),$E$6:$E$5523,0)) for the max value

    =INDEX(F7:$F$5523,MATCH(MIN(E6:E5523),$E$6:$E$5523,0)) for the min value

     

    See attached file

Aminnemati's avatar
Aminnemati
Copper Contributor
Jul 16, 2019

Twifoo 

Here is the sample file. But beware that the data in city column should be split to include only the city name (since the state and country has their own dedicated columns).

Roger Govier's avatar
Roger Govier
Brass Contributor
Jul 16, 2019

Aminnemati 

 

Hi

I inserted two columns after City, then selected column F and did Data > Text to Columns > Comma delimiter to split out State and Country. I then deleted the inserted columns G and H.

 

I inserted 4 rows at the top of the sheet and in cell F2 entered the formula

=INDEX(F6:$F$5523,MATCH(MAX(E6:E5523),$E$6:$E$5523,0)) for the max value

=INDEX(F7:$F$5523,MATCH(MIN(E6:E5523),$E$6:$E$5523,0)) for the min value

 

See attached file

  • Aminnemati's avatar
    Aminnemati
    Copper Contributor
    Jul 16, 2019

    Roger Govier 

    Hi Roger,

    Thank you for helping me in this. Have a nice day.

     

    Best regards

     

Resources