Vlookup function

[Eltun]

Dear Friends,

Please see the attached test file.
Could you be kind to help me with Vlookup function.

I need to get a value for A2 (sheet-Table) from C3:C1000 (sheet-Data) and which is different from value - A1.
I have written the following, but does not help:


=VLOOKUP(Data!C3:C1000<>Table!A1;Data!A3:C1000;3;FALSE)

Thanks for your assistance.

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

[Eltun]

Dear AliGW,

There is something wrong with attachment, as I click on paperclip icon, no message appears to upload the file. There is error, I guess.

[JohnTopley]

Use "Go Advanced" then "Manage Attachments" as "Paper Clip" icon no longer works (!!)

[Eltun]

Dear John,

Can anybody assist me with above case?

[AliGW]

The attachment doesn't show any expected results. Please explain clearly what the calculation should be and what an expected result would look like.

[Eltun]

I need to get a value for A2 in (sheet-Table) from C3:C1000 (sheet-Data) and which is different from value - A1.

The value 1,4980 is predetermined, I need to find the value from sheet-Data, range - C3:C1000 and which is different from 1,4980.
For example: 1,4980 is already on C23 and the following number is different, so I want to get value which is different from 1,4980

[oeldere]

Please Login or Register  to view this content.

The syntax of the vlookup formula is wrong.

I think you can solve your problem using a filter on your sheet data.

[Khalidngo]

Hi Eltun,

Not sure is this you are after:
=INDEX(Data!C3:C1000,MATCH(MAX(IF(Data!C3:C1000<>Table!A1,Data!C3:C1000)),Data!C3:C1000,0))

{array formula to be entered with Ctrl+Shift+Enter}

Blessing

[Eltun]

Thanks Oeldere,

Of course, the filter can be used anytime. But, system should be automated. Maybe, another function can be used instead of vlookup

[JohnTopley]

Try

=INDEX(Data!$C$3:$C$1000,SMALL(IF((Data!$C$3:$C$1000<>$A$1)*(Data!$C$3:$C$1000<>0),ROW($A$3:$C$1000),""),1))

Enter with Ctrl+Shift+Enter

will find first non-zero value which does not equal A1

[Eltun]

[QUOTE=Khalidngo;4373480]

I have used it, but gives an error: #VALUE!

[JohnTopley]

.... probably because you did not ....

array formula to be entered with Ctrl+Shift+Enter}

[Eltun]

.... probably because you did not ....

John, could you be kind to to do it for me and attach file, so I can check for my mistake

[JohnTopley]

See attached

[Eltun]

Dear John,

The problem is solved now
But, I need to add another critera that can be solved with Vlookup (I guess).

My main criteria is
1 to choose values from sheet-Data corresponding to data on B1, and to place it on D1
2 to choose values from sheet-Data corresponding to data on B2 and different from D1
3 to choose values from sheet-Data corresponding to data on B3 and different from D1 and D2
4 to choose values from sheet-Data corresponding to data on B4 and to ignore the criteria different from D1 and D2, if B4 is different from B1, B2, B3
5 and so on

pls check the attached file



I really appreciate your assistance, and will contact to you shortly (message)

[JohnTopley]

For
1. =VLOOKUP(B1,Data!A3:C1000,3,0)

2. =INDEX(Data!$C$3:$C$100,SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100<>$D$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1))

3. =INDEX(Data!$C$3:$C$1000,SMALL(IF((Data!$A$3:$A$1000=$B3)*(Data!$C$3:$C$1000<>$D$1)*(Data!$C$3:$C$1000<>$D$2)*(Data!$C$3:$C$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

Enter " and £ with Ctrl+ShifT+Enter

I have had problems with 3. and 4. in that I get a #NUM error. If I insert some data myself, I can get an answer so my logic appears OK.

4. =INDEX(Data!$C$3:$C$1000,SMALL(IF((Data!$A$3:$A$1000=$B4)*(Data!$C$3:$C$1000<>$D$1)*(Data!$C$3:$C$1000<>$D$2)*(Data!$C$3:$C$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

[JohnTopley]

For
1. =VLOOKUP(B1,Data!A3:C1000,3,0)

2. =INDEX(Data!$C$3:$C$100,SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100<>$D$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1))

3. =INDEX(Data!$C$3:$C$1000,SMALL(IF((Data!$A$3:$A$1000=$B3)*(Data!$C$3:$C$1000<>$D$1)*(Data!$C$3:$C$1000<>$D$2)*(Data!$C$3:$C$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

Enter 2 3 and 4 with Ctrl+ShifT+Enter

I have had problems with 3. and 4. in that I get a #NUM error. If I insert some data myself, I can get an answer so my logic appears OK.

4. =INDEX(Data!$C$3:$C$1000,SMALL(IF((Data!$A$3:$A$1000=$B4)*(Data!$C$3:$C$1000<>$D$1)*(Data!$C$3:$C$1000<>$D$2)*(Data!$C$3:$C$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

[Eltun]

Dear John,

Can you please attach the ready made file.
In the meantime, please check your inbox. Otherwise, mention your email, so I will contact you privately.

[JohnTopley]

See attached with my added data for 3.

No need to PM re this: contact via the thread.

[Eltun]

Dear John,

What if the second value is larger than the previous one?
You have used Small only, is it ok if I gonna use Large as well. because we don't know what the second value will be? smaller or larger.

I going to use the below equation, but does not give me any result:

2. =INDEX(Data!$C$3:$C$100,AND(SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100<>$D$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),LARGE(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100<>$D$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1)))

[JohnTopley]

SMALL isn't actual testing the data: it is simple used to find the first row where there is a value which meets the conditions in the IF.

It would help if explained the "purpose" behind these calculations: perhaps there is a another (better) solution.

[Eltun]

For example, there is data which is larger than the previous, and in this case, the formula does not give any result, that is why thought maybe this is because of SMALL function.
How we can solve the problem then, do you have any idea? or may be you can test it for me, please.

[JohnTopley]

this will do what you want...

=INDEX(Data!$C$3:$C$100,SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100>$E$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1))

Question: how do want to pick 1st item ...currently it is first value that matches your selection in B1

[Eltun]

John, it looks like now the formula looks for the value which is larger than previous ones. However, my concern is I should look for the first value in the column which different from E1.
The matter is the previous formula, which is
=INDEX(Data!$C$3:$C$100,SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100<>$E$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1))

finds the first value in the column different from E1, but at the same time smaller one. IF the date changes to be bigger than E1, it doesn't work. I have check for latest formula you set:


=INDEX(Data!$C$3:$C$100,SMALL(IF((Data!$A$3:$A$100=$B$2)*(Data!$C$3:$C$100>$E$1)*(Data!$C$3:$C$100<>0),ROW($A$3:$A$100)-ROW($A$3)+1,""),1)

And it also finds the first value in the column different from E1, but only larger.

So, I mean the data can be changed at any time, it can be larger or smaller, I just need to find the first in the column which different from E1.

[JohnTopley]

The original formulae did what you wanted i.e. checked it was not equal to D1 (and D2) and these could be larger or smaller.

[Eltun]

Jhon,

I have attached the original file, please check for sheet - Baza!; D5 (which is #N/A)

[JohnTopley]

Please attach file as there have been so many files added in this thread.

[Eltun]

it seems there was an error with attachment, pls find it below.

[Eltun]

Dear John,

Do you have any updates regarding the above request?

[JohnTopley]

Formula is wrong: range in ROW must be same as other ranges.

=INDEX(Baza!$G$3:$G$1000,SMALL(IF((Baza!$E$3:$E$1000=$B$2)*(Baza!$G$3:$G$1000<>$D$4)*(Baza!$G$3:$G$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

Not clear to me what you are doing in column C.

[Eltun]

Thanks for quick reply.

in column C, I am summarizing the all the values from column B (sheet: Baza) with the same branch code (column A - sheet: Baza) and the same rate (column C - sheet: Baza).

Example from sheet - Baza.

Code Amount Rate
100 100 1,4800
100 30 1,4800
100 500 1,4800
100 500 1,4800
100 50 1,4800
100 500 1,4800
100 100 1,4800
100 200 1,4800
100 100 1,4800
100 500 1,4800
100 50 1,4800
100 100 1,4800
100 200 1,4800
100 100 1,4800


As you can see the result I would like to get should look like 100 - 3030 - 1,4850.
For that reason, it is better to find rate first from sheet-Baza and paste it to Baza!; consequently rates following the first one and then based on rate to summarize all the amounts corresponding to the same branch code and rate.

If there is another way, please provide your solution.

Regards,

Eltun

[JohnTopley]

=SUMIFS(Baza!$B$3:$B$1000,Baza!$A$3:$A$1000,'Baza!'!$B$2,Baza!$C$3:$C$1000,'Baza!'!$B4)

No need for B2:E2

[Eltun]

Pls, check for D5 in Baza! as formulas does not work. But the same formula works for B5 in Baza!

[Eltun]

Dear John,

Do you have any updates regarding above issue?

[JohnTopley]

D5 inTest4.xlsx works OK for me : results is 1.496

[Eltun]

Sorry for disturbance, John.
But, the same formula results with N/A for me. Could you be kind to attache your work

[JohnTopley]

This is the formula

=INDEX(Baza!$G$3:$G$1000,SMALL(IF((Baza!$E$3:$E$1000=$B$2)*(Baza!$G$3:$G$1000<>$D$4)*(Baza!$G$3:$G$1000<>0),ROW($A$3:$A$1000)-ROW($A$3)+1,""),1))

[oeldere]

with a helpcolumn ((D) in sheet 2.

after that filter on the empty cells.

[Eltun]

Dear John,

Many thanks for assistance during all this time. All is done, and it is perfect. I have learned a lot during these days.

Thanks for your help again.

In the meantime, I need a minor help with VBA as I should copy and paste all those date from sheet Baza! to sheet FM. Do you suggest me to open a new threat or we should continue in here?

[JohnTopley]

I would start new thread and if it is definitely a VBA solution then post on the VBA/Macro forum.

Close this thread as solved ("Thread tools" at top of first post).