# VLOOKUP of values  COLUMN 1 and COLUMN 2



## vas6566 (Sep 24, 2019)

Hi you have shared very useful excel formula which was very helpful to me. I have some issues in applying VLOOKUP. If possible pls help to resolve the following;
I have 2 separate excel sheets both have same data in multiple columns but not in the same sequence. I paste the sheet 1 below



A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000


<tbody>

</tbody>
SHEET2



A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000


<tbody>

</tbody>
Sheet1 was prepared by one person and Sheet2 was generated by another person. it has got thousands of rows.
I have to match CODE and BILL NO. in the rows of Sheet1 with the CODE and BILL NO. in sheet2. In simple I have to get the  VALUE  of sheet2 against the same CODE AND same BILL NO. in sheet1.  This will ensure that value against each BILL NO. is same in both the sheets ( sheet1 and sheet2) kindly share suitable excel formula.
thanks in advance


----------



## Special-K99 (Sep 24, 2019)

Try

in Sheet1!F2
=LOOKUP(2,1/(Sheet2!C$2:C$100000=C2)*(Sheet2!D$2:D$100000),(Sheet2!E$2:E$100000))
and copy down Sheet1 column F


----------



## vas6566 (Sep 25, 2019)

Hi Special-K99,
 Thanks for your valuable effort and reply. I copied the formula in Sheet1!F2, it shows #N/A. I think you have tried maximum. Can you pls try the same in copying the data in sheet1 and sheet2 and apply the formula in Sheet 1, F2. If you get better result, kindly share with me. Thanks a lot for extending helping hand to someone unknown.


----------



## Fluff (Sep 25, 2019)

How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&D2,INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))


----------



## vas6566 (Sep 26, 2019)

Dear [FONT=&quot]Fluff ,
yea it worked , it worked,  You have done it, Hats off dear Fluff.  [/FONT]ray:[FONT=&quot] I find no words to thank you,  Rather I never used such formula so far.  I was really struggling  until i got your formula.  Mere a word of "thank you" wont be sufficient at this moment. Thanks in million. please be helpful to others like this, heavenly graces be with you. [/FONT]


----------



## Fluff (Sep 26, 2019)

Glad we could help & thanks for the feedback


----------



## vas6566 (Sep 26, 2019)

Dear Fluff,

is it possible to apply wildcard in the column where BILL No. is listed. Because in Sheet 2 some bills nos  are with hyphen, some bills are  prefixed with alphabets.

for example pls refer the bills column mentioned below.  thanks in advance
SHEET2



ABCDEDATENAMECODEBILL NO.VALUE05-04-2019JOYAFI704600006-04-2019VIVAAFO115500003-04-2019MINIAAFG-103550004-04-2019JOYAFI703F700001-04-2019MINIAAF101500002-04-2019SONYAAA4022000

<tbody>

</tbody>


----------



## Fluff (Sep 26, 2019)

How about
=INDEX(Sheet2!$E$2:$E$7,MATCH(C2&"|"&"*"&D2&"*",INDEX(Sheet2!$C$2:$C$7&"|"&Sheet2!$D$2:$D$7,0),0))


----------



## vas6566 (Sep 27, 2019)

Dear Fluff,

ray:ray:ray:. i got it completely this time.  I dont know how to thank you, you are helping some one from far away. I applied the formula to almost 9800 rows, everything worked fine. I am able to identify the rows with mismatch easily. Of course your help has given big help not only for my ongoing work, but also to the one i may face in future.  You must be having big heart to respond quickly with alternative solution. you responded very quickly , but i , as a recipient of help,could not send thanking message to you at the same speed due to my work. Thanks fluff, once again no words. Hope you are a valuable member to this group. see you


----------



## Fluff (Sep 27, 2019)

You're more than welcome & thanks for the feedback


----------



## vas6566 (Sep 24, 2019)

Hi you have shared very useful excel formula which was very helpful to me. I have some issues in applying VLOOKUP. If possible pls help to resolve the following;
I have 2 separate excel sheets both have same data in multiple columns but not in the same sequence. I paste the sheet 1 below



A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000


<tbody>

</tbody>
SHEET2



A
B
C
D
E
DATE
NAME
CODE
BILL NO.
VALUE
05-04-2019
JOY
AFI
704
6000
06-04-2019
VIVA
AFO
115
5000
03-04-2019
MINI
AAF
103
5500
04-04-2019
JOY
AFI
703
7000
01-04-2019
MINI
AAF
101
5000
02-04-2019
SONY
AAA
402
2000


<tbody>

</tbody>
Sheet1 was prepared by one person and Sheet2 was generated by another person. it has got thousands of rows.
I have to match CODE and BILL NO. in the rows of Sheet1 with the CODE and BILL NO. in sheet2. In simple I have to get the  VALUE  of sheet2 against the same CODE AND same BILL NO. in sheet1.  This will ensure that value against each BILL NO. is same in both the sheets ( sheet1 and sheet2) kindly share suitable excel formula.
thanks in advance


----------



## Special-K99 (Sep 27, 2019)

vas6566 said:


> Hi Special-K99,
> Thanks for your valuable effort and reply. I copied the formula in Sheet1!F2, it shows #N/A. I think you have tried maximum. Can you pls try the same in copying the data in sheet1 and sheet2 and apply the formula in Sheet 1, F2. If you get better result, kindly share with me. Thanks a lot for extending helping hand to someone unknown.



Oops! I left out a set of brackets.
This works (tested)

in Sheet1!F2
=LOOKUP(2,1/((Sheet2!C$2:C$100000=C2)*(Sheet2!D$2:D$100000)),(Sheet2!E$2:E$100000))
and copy down the column


----------



## vas6566 (Jan 4, 2023)

Fluff said:


> You're more than welcome & thanks for the feedback


Hi, HAPPY NEW YEAR.  I have come back to you after 2019 with a mild hiccup in creating one excel formula perfectly. Hope you can resolve it.  I need a suitable formulae for the following subject.

IF cell B3 contains the value "A", at the same time only if cell H3 has a value greater than zero, it should return D3+E3-G3.  that is if H3 has no value or its value is less than zero, no result is required.  Here 2 conditions need to be fulfilled, the value in B3 should be exactly "A" and at the same time value in H3 should numeric value more than 0, if these two conditions are satisfied i need the value  of D3+E3-G3, if these two conditions are not satisfied no value is required in the formula cell ( not necesary to add D3 and E3 and less G3 ).  The following table may be self explanatory. pls respond , thank in advance


PARTICULARSUNITNet Block as per B/S- 01.04.2022Gross Block_AYM_ 1.4.2022AdditionsDepDeletionsNet Block as per B/S- 31-03-2023Gross Block_AYM_ 31.03.2023Result if formula is correctBuilding-HosurA3,84,745.00500000​1000​0​3,85,745.00formula required501000​Factory-Ayanambakkam New BuildingA9,44,195.041500000​55000​0​9,00,195.00formula required1555000​Factory Buildg - (Mogappair A10 & B8)M94,60,755.000​0​94,60,755.00formula required0​Factory Building-Ayanambakkam - OldA1,90,120.001140000​190120​-formula required0​Factory- Building- Sipcot, VallamA25,50,000.003000000​200000​50000​27,00,000.00formula required3150000​


----------



## Fluff (Jan 4, 2023)

As this is a totally different question, it needs a new thread. Thanks


----------

