232showtime

EXCEL FORMULA

4 posts in this topic

#1 ·  Posted

Hi all,

I got a formula from google which is vlook up from right to left,

VLOOKUP(B1,IF({1,0},CheckList!$C$2:$C$398,CheckList!$B$2:$B$398),2,0),6) ;<-- this is excel formula

Im planning to add this to my autoitscript, using native command like:

$oExcel.WorksheetFunction.VLookup

I want to understand the meaning of "{1,0}" in my excel formula


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

That formula does not look right as there are 3 closing brackets and only 2 functions.
On top of that the lookup table only has one column and you want to retrieve column 2.

Is this the original you found or did you change it yourself? .. and what are you really looking for here as I am not sure what you mean with a VLOOKUP from right to left as V stands for Vertical.

Jos

EDIT: And a quick Google search gave the answer to the {1,0} question: http://www.excelhowto.com/functions/if-function/

 

Edited by Jos
1 person likes this

Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

#3 ·  Posted

formula is correct and working properly, I just changed the excel ranged.  I found it here

i tried the second example from the site you've given and got #VALUE! output. I still did not get how the " {1,0} " works, can you explain in words?

=IF(A1=B1,TRUE,FALSE) <-- because A1=B1 so its true

but

=IF({1,0},TRUE,FALSE) <--- how did it become true?, where did he got the information that makes this statement true?

I'm confused, need some expert guide. :sweating::sweating::sweating:

 


ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

#4 ·  Posted

I looked at your linked site and see that indeed that last part of the formula isn't there => ",6)" making it invalid for me.

VLOOKUP(B1,IF({1,0},CheckList!$C$2:$C$398,CheckList!$B$2:$B$398),2,0),6) ;<-- this is excel formula

So what your statement should now do is the following:

It check for the value of B1 in the table cells $C$2:$C$398 and will return the value from the B column.
I don't think you can use the statement  =IF({1,0},TRUE,FALSE)  just like that as it doesn't mean much.

Jos

 


Visit the SciTE4AutoIt3 Download page for the latest versions        Beta files                                                          Forum Rules
 
Live for the present,
Dream of the future,
Learn from the past.
  :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now