Lookup Problem With Long Argument

1. Lookup Problem With Long Argument

hi all...

i have 2 formula how to combine them into single formula :

INDEX(\$L\$2:\$L\$31;MATCH(A17;\$K\$2:\$K\$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1)))

i want this below formula can be inserted in above formula
=VLOOKUP(I2;master!\$A\$2:\$O\$5;6;FALSE)

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)))

my target in cell D17 down and with number lookuo in cell I2, my data source/reference in sheet "master"

this attachment file

any help, greatly appreciated..

2. Re: 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)

3. Re: Lookup Problem With Long Argument

hi Ford..

i have trying :

=IF(I2;TRUE;VLOOKUP(I2;master!A2:E5;4;INDEX(\$L\$2:\$L\$31;MATCH(A17;\$K\$2:\$K\$31;0)+IF(Hitung1!D23>0;0;IF(Hitung1!D23=0;2;1)))))

still not work....would you help me?

4. Re: Lookup Problem With Long Argument

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?

5. Re: Lookup Problem With Long Argument

hi Ford,...i choose to use vlookup

6. Re: Lookup Problem With Long Argument

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?

7. Re: Lookup Problem With Long Argument

yes, you're correct i want into 1 formula...

before it, you have help me, in this post
https://www.excelforum.com/excel-for...ia-number.html

i have modify and how to joint 2 formula ito 1 formula

8. Re: Lookup Problem With Long Argument

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.

9. Re: Lookup Problem With Long Argument

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?

10. Re: Lookup Problem With Long Argument

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

``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.

11. Re: Lookup Problem With Long Argument

hi sktneer....

this my new sample...
my target in D17 down ...yellow & blue shading to easy looked.
my data in sheet "master" in blue shading..
lookup number as parameter in cell I2...
when i type or change number in cell I2, the data display change too depend data in sheet "master"

sorry my english is not fluent, it's my problem..

12. Re: Lookup Problem With Long Argument

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.

13. Re: Lookup Problem With Long Argument

Hi.

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

14. Re: Lookup Problem With Long Argument

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?

15. Re: Lookup Problem With Long Argument

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.

16. Re: Lookup Problem With Long Argument

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.

17. Re: Lookup Problem With Long Argument

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.)

18. Re: Lookup Problem With Long Argument

Originally Posted by sktneer
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.
hi sktneer....thank you so much for your time & effort!!
working well 100%...

19. Re: Lookup Problem With Long Argument

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...

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

20. Re: Lookup Problem With Long Argument

sktneer, thanks for the assist on this

21. Re: Lookup Problem With Long Argument

No problem Ford!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1