Lookup Problem With Long Argument

I thought that formula looked familiar

What would be the requirement for choosing the vlookup part, instead of the INDEX part?

Normally with something like this, it is a case of...
=if(test1=TRUE, do vlookup, else do index)

Sorry, my example was to show what the syntax would look like, not how the actual formula would look

You have 2 formulas to choose from (vlookup and INDEX). What would the rule be to decide which 1 to use?

No, you misunderstand. If I am correct, you want to combine the vlookup and index into 1 formula, correct?

i have trying to combine it but not working :
=VLOOKUP(I2;master!$A$2:$O$5;6;FALSE);INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1)))

So based on that, you want the formula to make a decision on which 1 to use.

How would I know that you want "0" (from the vlookup) or "diberikan penyesuaian min/negatif (-) karena lokasinya lebih baik dibandingkan objek penilaian" from the index?

Yes I understand you want 1 formula.

What I am trying to get you to explain to me is how do I decide which 1 to use?

Under what conditions would I use the vlookup, and when would I use the index?
What would the rule be?

for example, if it is Wednesday, I use the vlookup
if it is not Wednesday, I use index.

Is it to do with the value in cell I2? If it's 1 use INDEX, if it's 2 use VLOOKUP? Is that it, Jhon?

Are you trying to concatenate the output returned by VLookup and Index formula?

If so, please try this...

Please Login or Register  to view this content.

If that's not what you are trying to achieve, please upload a sample file and mock up the desired output manually in a cell next to the formula cell to show what exactly you are trying to achieve.

Sorry it is still not clear.

e.g. in the sample file you only placed your Index formula in D17. How this formula depends upon what you enter in I2 and how your VLookup formula fits here?

Also, in the following part of your index formula, you have used relative cell references which will change if you drag down the formula in D17. What's the logic behind this?
IF(master!F2>0,0,IF(master!F2=0,2,1))

So you are saying that the formula output in D17 should change if you change the value in I2 but the question remains the same i.e. how?

I am attaching your file again for you to enter your expected formula output in below cells...

G17 --> Enter your desired output if the value in cell I2 is 1.
H17 --> Enter your desired output if the value in cell I2 is 2.
I17 --> Enter your desired output if the value in cell I2 is 3.

While manually populating the desired output in G17:I17, explain the logic behind your desired output to show why and how the desired output depends on value in I2.


After populating the cells G17:I17 with your desired outputs that depend on what you enter in I2, it will help us to visualize the difference between the output returned by the existing Index formula in D17 which is not dependent upon the value in I2 and your desired outputs.

Also, language shouldn't be a barrier here. If you are not able to explain it properly, you can show us your desired output for different scenarios and to do that you can duplicate the sheet "kertas kerja" few times in the sample workbook, change the value in I2 on each duplicate sheet and manually show us the desired output in column G for the changed value in I2 and explain the logic as much as possible.

Hi.

In g17---- result K
H17------- result J
I17------reault L

Okay, this is my last attempt to ask you about the logic behind your desired output.

Forget about the formula and let me know what steps you would manually take to know the desired output considering there is 2 in I2.

I just want your steps in layman terms...

e.g.

Assume that I2=2 then...

To get the desired output manually, you will first look at the value in A17 which is "Lokasi", then what?
What would be your next steps to get to the desired output and what do you look in Master sheet and what role has value in I2 (say which is 2) in finding the desired output?

okay sktneer...

i try to explain it :

before, i have a formula that contains :
Instead number (positive, negative, nol) to be text as reference please see in cell M2 down in sheet "kertas kerja". 3 criteria number always sequence from top to down and always repetition..

my data in sheet "master" and to convert number to be text (based on data number in sheet "master" see in F2 down right in sheet "master") so i use formula =INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1))) to instead number it

then i want to use lookup formula to combine or into in a 1 formula or anything..that data can display based on lookup number in cell I2 in sheet "kertas kerja"

for easy & detail explained , i have modify my new sample with manually result that i want...when I2=1, I2=2 or I2=3 etc....down.

Please try this and see if this is what you are trying to achieve...

In D17

Please Login or Register  to view this content.

and then copy it down.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

You're welcome Jhon!

Did you pay attention to the administrative post of Alan?

Posting the same question in other forums is against the forum rule 3 and Alan only asked you to post the question links from other forum here itself but it seems you overlooked that post.

Anyways, you can still post those links here. I would appreciate if you find some time to do that now.


All we expect from you to post here like this....

****************************************

I opened the same question here also...

and post question links here...

****************************************

sktneer, thanks for the assist on this

No problem Ford!