VLOOKUP of values COLUMN 1 and COLUMN 2

vas6566

New Member
Joined
Sep 24, 2019
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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


[TABLE="width: 342"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
</tbody>[/TABLE]

SHEET2


[TABLE="width: 340"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]DATE
[/TD]
[TD]NAME
[/TD]
[TD]CODE
[/TD]
[TD]BILL NO.
[/TD]
[TD]VALUE
[/TD]
[/TR]
[TR]
[TD]05-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]704
[/TD]
[TD]6000
[/TD]
[/TR]
[TR]
[TD]06-04-2019
[/TD]
[TD]VIVA
[/TD]
[TD]AFO
[/TD]
[TD]115
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]03-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]103
[/TD]
[TD]5500
[/TD]
[/TR]
[TR]
[TD]04-04-2019
[/TD]
[TD]JOY
[/TD]
[TD]AFI
[/TD]
[TD]703
[/TD]
[TD]7000
[/TD]
[/TR]
[TR]
[TD]01-04-2019
[/TD]
[TD]MINI
[/TD]
[TD]AAF
[/TD]
[TD]101
[/TD]
[TD]5000
[/TD]
[/TR]
[TR]
[TD]02-04-2019
[/TD]
[TD]SONY
[/TD]
[TD]AAA
[/TD]
[TD]402
[/TD]
[TD]2000
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
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
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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 correct
Building-HosurA3,84,745.00
500000​
1000​
0​
3,85,745.00formula required
501000​
Factory-Ayanambakkam New BuildingA9,44,195.04
1500000​
55000​
0​
9,00,195.00formula required
1555000​
Factory Buildg - (Mogappair A10 & B8)M94,60,755.00
0​
0​
94,60,755.00formula required
0​
Factory Building-Ayanambakkam - OldA1,90,120.00
1140000​
190120​
-formula required
0​
Factory- Building- Sipcot, VallamA25,50,000.00
3000000​
200000​
50000​
27,00,000.00formula required
3150000​
 
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top