how to use vlookup return only some condition satisfies

[gvgbabu]

hi
i have an exel sheet with lot of data.
i want to use vlookup function for getting what cell i want. the same time it will also check if and only if two cells are matching then return the result.
is it possible. please guide me.

for example:

101 3.2 7.3 200 500
102 5.2 8.3 200 450
103 3.2 7.3 200 750
101 7.3 7.3 200 450
102 7.3 7.3 200 500

103 7.3 7.3 200 600

if my lookup_value is 103(ie A3 ) in this table array and required column is 6th column(ie E6). return the value if and only if B & C cells of the corresponding row matches. In the example B6=C6 then return the E6=600.
if i am using without this condition it returns the same row values ie E3=750 required is E6=600
gvg

[FDibbins]

I think you will need to upload a sample workbook, showing what you have and what you want?

[gvgbabu]

hi FDibbins,

thank you for you reply
i am attaching here the excel sheet
i have coloured in the excel sheet
i am getting wrong results as i explained in the previous post.
while vlookup it has to satisfy the condition C = J of the matched row.
i thing it can be done by two ways but i dont know is it possible or not.
i)to exclude row which have lookup_value
ii) any conditional with vlookup

please guide me

[blueblink01]

hi gvgbabu ,

i'm a bit confused in your worksheet. you can use the "if" function for conditions (vlookup)

can you insert column with your required result? many in the forum can help you.

Thanks

[gvgbabu]

hi

i explain with example

this is the 419 row in the sheet

1089 414 20.25 5.65 -8.46 #N/A #N/A #N/A 416 24.38 5.65 -8.46 230 600 0 4130 230 725 414 230 725 416 230 425

lookup_value:414
required columns: Q & R

but it lookup from B2 and selecting the same row which has looup value and returns 230 725.

the required rows while lookup are 429 and 430 row numbers

for lookup_value 414 this is row and retrun 230 425

1099 414 20.25 5.65 -8.46 #N/A #N/A #N/A 425 20.25 5.65 -6.23 #N/A #N/A #N/A 2230 230 425 414 230 725 425 230 725

for lookup_value 416 this is row and retrun 230 425

1100 416 24.38 5.65 -8.46 230 600 0 426 24.38 5.65 -6.23 300 600 0 2230 230 425 416 230 425 426 230 500

[sktneer]

Something like this........

Array Formula in T2 (requires Ctrl+Shift+Enter not only Enter)

Please Login or Register  to view this content.

[gvgbabu]

hi sktneer

thank you for your reply
but i am not getting correct value.

please explain the arrayt in detail .

thanks
gvg

[sktneer]

To understand what is this array formula doing, use this formula on a small set of data. In the attached workbook, I have taken the example you posed in your post#1. In the formula lookup value is 103. Notice what is the difference between the regular formula (yours) and array formula.

[gvgbabu]

its giving the correct result while checking with my first post.
when i am checking with my excel sheet "input" its not giving correct results.
please check with my input sheet

[sktneer]

First you must understand what this formula is doing.
what it does, it finds all the rows where lookup value 103 is in col. A and col. B and Col. C have the same value, if there are one or more such rows, it returns the corresponding value of col. E for from the first row.

The formula works same in your input sheet. It find all the rows with lookup value where col. C and Col. J have the same value and return the value from the column with col.index in the formula.

So in the formula.....

Please Login or Register  to view this content.

The formula finds all the rows in B2:B15000 where the lookup value is equal to B2. If it finds one or more such rows, It checks further that the corresponding values in col. C and col. J are same and if it finds any such rows, it returns the corresponding value from col. Q from the first row it finds.

[gillani77]

hi
i m having same problem but i need to get data from other sheet. Can i use this formula. here is details of my problem.
HI Sir,
I have 2 sheets in same spread sheet with following columns in each sheet.
1. zone
2. date
3. representative
4. commission earned
I want to copy commission earned on sheet 1 column “Y”( namely RICARDO)from sheet to 2 (namely OCT – FEB) ,if following condition is fulfilled/ satisfied.
All these values (Zone , date, representative and commission) of both sheets is same then commission of 2nd sheet should be copied on sheet 1 column “Y”

I am using excel 2010 , window8 and an HP lap top.
If you dont find my up loaded file in bluebox, pls let me know how can I send you my sheet. I had tried to take print shot but it did not work. Please help me out in this matter thanks.
I have uploaded/ shared my file on following link.

https://app.box.com/s/g3jip0ky22it0ack9xov

if you donot finf my sheets, please advise me how to attach sheets on this forum.
thanks

Kind regards.

[FDibbins]

gillani77 welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.


Also, please upload your sample workbook to this forum, not all members are able - or willing - to access file-hosting web sites

[gillani77]

Hi FDibbins,
thanks for your advice. I am going to post my problem in new thread.