zois

2015-02-03, 02:11 PM

i have a table A1:P63, i want to show me A where P>64,i did this with query function but in 365 don't see query....how can i do it?

View Full Version : query function in office 365 excel?

zois

2015-02-03, 02:11 PM

i have a table A1:P63, i want to show me A where P>64,i did this with query function but in 365 don't see query....how can i do it?

NBVC

2015-02-03, 02:50 PM

What do you mean by the "query function"?

zois

2015-02-03, 02:58 PM

i forgot to say that i worked in goggle spreadsheets, the function was =query(A1:P63;"select A where P>64")

NBVC

2015-02-03, 03:02 PM

What does that function do?

Edit. I just googled the function. Excel does not have an equivalent function... but depending on what you are doing there should be other ways.

Please explain what you are trying to accomplish. Are there more than 1 results possible?

example.... if there is only 1 result expected.. you can use:

=INDEX($A$1:$A$63,MATCH(TRUE,$P$1:$P$63>64,0))

confirmed with CTRL+SHIFT+ENTER.

If there are multiple results possible.

=IFERROR(INDEX($A$1:$A$63,SMALL(IF($P$1:$P$63>64,ROW($P$1:$P$63)-MIN(ROW($P$1:$P$63))+1),ROWS($X$1:$X1))),"")

confirmed with CTRL+SHIFT+ENTER, and copied down

Edit. I just googled the function. Excel does not have an equivalent function... but depending on what you are doing there should be other ways.

Please explain what you are trying to accomplish. Are there more than 1 results possible?

example.... if there is only 1 result expected.. you can use:

=INDEX($A$1:$A$63,MATCH(TRUE,$P$1:$P$63>64,0))

confirmed with CTRL+SHIFT+ENTER.

If there are multiple results possible.

=IFERROR(INDEX($A$1:$A$63,SMALL(IF($P$1:$P$63>64,ROW($P$1:$P$63)-MIN(ROW($P$1:$P$63))+1),ROWS($X$1:$X1))),"")

confirmed with CTRL+SHIFT+ENTER, and copied down

zois

2015-02-03, 03:22 PM

here an example:

https://docs.google.com/spreadsheets/d/12xnQpRiyVxfTPKUX-8RIDPH27hMqYYJc1FYLAoQruqQ/edit?usp=sharing

in column D show me the values from column A if the values in column B is>64.in excel how can i do that?

https://docs.google.com/spreadsheets/d/12xnQpRiyVxfTPKUX-8RIDPH27hMqYYJc1FYLAoQruqQ/edit?usp=sharing

in column D show me the values from column A if the values in column B is>64.in excel how can i do that?

NBVC

2015-02-03, 03:33 PM

I am unable to open that link.. but check the second formula I posted in my last post above. Replace the $P$1:$P$63 with column B references... replace the $X$1:$X1 with $D$1:$D1

Remember that after typing in the formula, hold the CTRL and SHIFT keys down whilst hitting ENTER. You will see { } brackets appear around the formula, then you can copy formula down.

Remember that after typing in the formula, hold the CTRL and SHIFT keys down whilst hitting ENTER. You will see { } brackets appear around the formula, then you can copy formula down.

zois

2015-02-03, 03:50 PM

yes i did that and work fine.thank you very much for your answer!

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.