VBA VLOOKUP from multiple columns

N

Neveidas

New Member
Joined
Oct 6, 2021
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Hi guys, I'm trying to VLOOKUP from multiple columns, so I would need to VLOOKUP Column A referencing Column H and I, and have the location placed in Column C. But it would also need to look up at Column B and if the name of the company is "OPPO Company", then Column C's value would be OPPO-FLEET, it also needs to look up Column D and if it's "Forward to shipping partner", then Column C's value will be "Cross-Border Export"

Sorry if it sounds messy, I hope I explained it right

Main Template.xlsm
ABCDEFGHI
1postal codeLocationStatusPostcodePickup Hub
253ABC CompanyCSLDelivered01PJ
354ABC CompanyCSLDelivered02PJ
456ABC CompanyCSLDelivered03PJ
582ABC CompanyCSLDelivered04PJ
652ABC CompanyCSLDelivered05PJ
765ABC CompanyDelivered06PJ
882ABC CompanyDelivered07PJ
932ABC CompanyDelivered08PJ
1064ABC CompanyDelivered09PJ
1182ABC CompanyDelivered10PJ
1246ABC CompanyDelivered11PJ
1379ABC CompanyDelivered12PJ
14L8ABC CompanyCross-Border ExportForward to Shipping Partner13PJ
1588ABC CompanyCross-Border ExportForward to Shipping Partner14PJ
1685ABC CompanyCross-Border ExportForward to Shipping Partner15PJ
1798ABC CompanyCross-Border ExportForward to Shipping Partner16PJ
1888ABC CompanyCross-Border ExportForward to Shipping Partner17PJ
1983ABC CompanyCross-Border ExportForward to Shipping Partner18PJ
20HKXYZ CompanyCross-Border ExportForward to Shipping Partner19PJ
2185XYZ CompanyCross-Border ExportForward to Shipping Partner20PJ
22TaXYZ CompanyForward to Shipping Partner21PJ
2396XYZ CompanyForward to Shipping Partner22PJ
2486XYZ CompanyForward to Shipping Partner23PJ
2586XYZ CompanyForward to Shipping Partner24PJ
2687XYZ CompanyForward to Shipping Partner25PJ
2788XYZ CompanyForward to Shipping Partner26PJ
2853OPPO CompanyOPPO-FLEETDelivered27PJ
2954OPPO CompanyOPPO-FLEETDelivered28PJ
3056OPPO CompanyOPPO-FLEETDelivered29PJ
3182OPPO CompanyOPPO-FLEETDelivered30PJ
3252OPPO CompanyOPPO-FLEETDelivered31PJ
3365OPPO CompanyOPPO-FLEETDelivered32PJ
3482OPPO CompanyOPPO-FLEETDelivered33CSL
3532OPPO CompanyDelivered34CSL
3664OPPO CompanyDelivered35PJ
3736CSL
3837CSL
3938CSL
4039CSL
4140CSL
4241CSL
4342CSL
4443CSL
4544CSL
4645CSL
4746CSL
4847CSL
4948CSL
5049CSL
5150CSL
5251CSL
5352CSL
5453CSL
5554CSL
5655CSL
5756CSL
5857PJ
5958PJ
6059PJ
6160PJ
6261PJ
6362PJ
6463PJ
6564PJ
6665PJ
6766PJ
6867PJ
6968PJ
7069PJ
7170PJ
7271PJ
7372PJ
7473PJ
7575PJ
7676PJ
7777PJ
7878PJ
7979CSL
8080CSL
8181CSL
8282CSL
Sheet3
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
1) It is so simple. Can formula solution only? No need VBA. or VBA only solution?
2) What if "OPPO " and "Forward" at the same row?
3) In column A and H, are they still value_stored_as Text?
 
Upvote 0
bebo021999 said:
1) It is so simple. Can formula solution only? No need VBA. or VBA only solution?
2) What if "OPPO " and "Forward" at the same row?
3) In column A and H, are they still value_stored_as Text?
Click to expand...
any solution is fine good sir
as for the second question, they won't be on the same row at all,
Yes, sir, they are still stored as text
 
Upvote 0
Try:
C2:
=IFERROR(IF(B2="OPPO COMPANY","OPPO-FLEET",IF(LEFT(D2,7)="Forward","Cross-Border Export",VLOOKUP(LEFT(B2,2),$H$2:$I$10000,2,0))),"NO MATCH")
 
Upvote 0
Solution
bebo021999 said:
Try:
C2:
=IFERROR(IF(B2="OPPO COMPANY","OPPO-FLEET",IF(LEFT(D2,7)="Forward","Cross-Border Export",VLOOKUP(LEFT(B2,2),$H$2:$I$10000,2,0))),"NO MATCH")
Click to expand...
it is working, but instead of "NO MATCH", is it possible to have it filled with either "PJ" or "CSL" from Column H and I based on the postal?
 
Upvote 0
Its a case of row 22, code="Ta" and XYZ company, its "NO MATCH". What would you like to put in C22?
 
Upvote 0
bebo021999 said:
Its a case of row 22, code="Ta" and XYZ company, its "NO MATCH". What would you like to put in C22?
Click to expand...
Because it's Shipping to partner, C22 can remain as it is

so if There is a forward to shipping partner in Column D, Column C will be Cross-Border Export
If there is a "OPPO Company" in Column B, Column C will be "OPPO FLEET"
Whichever doesn't have either of those, would VLOOKUP from Column H and I, with their postal code
 
Upvote 0
It seems my formula in #4 is working well.
If not, put my formula into your file, highlight the expected result then upload agian.
 
Upvote 0
bebo021999 said:
It seems my formula in #4 is working well.
If not, put my formula into your file, highlight the expected result then upload agian.
Click to expand...
yeah it is, just that the first few would be either PJ or CSL from the VLOOKUP table in Column H and I instead

I have highlighted the expected results

Main Template.xlsm
ABCDEFGHI
1postal codeLocationStatusPostcodePickup Hub
253ABC CompanyCSLDelivered01PJ
354ABC CompanyCSLDelivered02PJ
456ABC CompanyCSLDelivered03PJ
582ABC CompanyCSLDelivered04PJ
652ABC CompanyCSLDelivered05PJ
765ABC CompanyPJDelivered06PJ
882ABC CompanyCSLDelivered07PJ
932ABC CompanyPJDelivered08PJ
1064ABC CompanyPJDelivered09PJ
1182ABC CompanyCSLDelivered10PJ
1246ABC CompanyCSLDelivered11PJ
1379ABC CompanyCSLDelivered12PJ
14L8ABC CompanyCross-Border ExportForward to Shipping Partner13PJ
1588ABC CompanyCross-Border ExportForward to Shipping Partner14PJ
1685ABC CompanyCross-Border ExportForward to Shipping Partner15PJ
1798ABC CompanyCross-Border ExportForward to Shipping Partner16PJ
1888ABC CompanyCross-Border ExportForward to Shipping Partner17PJ
1983ABC CompanyCross-Border ExportForward to Shipping Partner18PJ
20HKXYZ CompanyCross-Border ExportForward to Shipping Partner19PJ
2185XYZ CompanyCross-Border ExportForward to Shipping Partner20PJ
22TaXYZ CompanyCross-Border ExportForward to Shipping Partner21PJ
2396XYZ CompanyCross-Border ExportForward to Shipping Partner22PJ
2486XYZ CompanyCross-Border ExportForward to Shipping Partner23PJ
2586XYZ CompanyCross-Border ExportForward to Shipping Partner24PJ
2687XYZ CompanyCross-Border ExportForward to Shipping Partner25PJ
2788XYZ CompanyCross-Border ExportForward to Shipping Partner26PJ
2853OPPO CompanyOPPO-FLEETDelivered27PJ
2954OPPO CompanyOPPO-FLEETDelivered28PJ
3056OPPO CompanyOPPO-FLEETDelivered29PJ
3182OPPO CompanyOPPO-FLEETDelivered30PJ
3252OPPO CompanyOPPO-FLEETDelivered31PJ
3365OPPO CompanyOPPO-FLEETDelivered32PJ
3482OPPO CompanyOPPO-FLEETDelivered33CSL
3532OPPO CompanyOPPO-FLEETDelivered34CSL
3664OPPO CompanyOPPO-FLEETDelivered35PJ
3736CSL
3837CSL
3938CSL
4039CSL
4140CSL
4241CSL
4342CSL
4443CSL
4544CSL
4645CSL
4746CSL
4847CSL
4948CSL
5049CSL
5150CSL
5251CSL
5352CSL
5453CSL
5554CSL
5655CSL
5756CSL
5857PJ
5958PJ
6059PJ
6160PJ
6261PJ
6362PJ
6463PJ
6564PJ
6665PJ
6766PJ
6867PJ
6968PJ
7069PJ
7170PJ
7271PJ
7372PJ
7473PJ
7575PJ
7676PJ
7777PJ
7878PJ
7979CSL
8080CSL
8181CSL
8282CSL
Sheet3
Cell Formulas
RangeFormula
C14:C36C14=IFERROR(IF(B14="OPPO COMPANY","OPPO-FLEET",IF(LEFT(D14,7)="Forward","Cross-Border Export",VLOOKUP(LEFT(B14,2),$H$2:$I$10000,2,0))),"NO MATCH")
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Question
SCHEDULE DASHBOARD VBA CODE (AUTOMATCICALLY TOGGLE TO MATCHING COLUMN)
Replies
0
Views
98
Willkim
W
F
  • Solved
How to format a vendor list when vendors offer several different products?
Replies
8
Views
217
Micron
M
B
  • Question
Copying Data from Sheet 1 to Sheet 2 and adding rows- Code Needed
2 3 4
Replies
30
Views
866
mumps
mumps
S
  • Solved
Copy formula down a table column, but skip used cells in that column
Replies
4
Views
319
sspatriots
S
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
447
samide2001
S

Forum statistics

Threads
1,215,459
Messages
6,124,947
Members
449,198
Latest member
MhammadishaqKhan

Share this page

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