Vlookup to check matching items, If also bought from 'Sarqusan', 'Sarqusan' in column D

T

toony

Board Regular
Joined
Mar 20, 2014
Messages
91
Office Version
  1. 365
Hi

I have 2 suppliers, Anglian and Sarqusa and we buy some of the items from both suppliers and want to find out which items that we buy from the secondary supplier (Sarqusan) we use. Columns A to C is the main excel I use with our main suppliers like Anglian. Then I have another report with loads of other parts only for Sarqusan. It's very long list and wanted to use the Vlook up formula to identify which items appear in the Sarqusan report that we are already buying from Anglian if that makes sense, in the column D as below. What would you advise. I've tried using the Vlook up formula so that Sarqusan appears in column D whenever it's also bought from Sarqusan. I have used it in the past but can't remember. There are probably easier ways to find the info which I would like to know as well but need to know how to use the Vlookup formula as well please. I really appreciate your help. Thanks


<tbody>
[TD="align: left"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]

[TD="align: left"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Material[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: left"]Vendor name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: left"]Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"] Vlookup
Form [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]
[/TD]
[TD="align: left"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: left"]Vendor name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]Material[/TD]

[TD="align: right"]1[/TD]
[TD="align: center"]RM611266[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cable[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM075005[/TD]

[TD="align: right"]2[/TD]
[TD="align: center"]RM075005[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Crossy[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM611807[/TD]

[TD="align: right"]3[/TD]
[TD="align: center"]RM604663[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Pump[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM61155[/TD]

[TD="align: right"]4[/TD]
[TD="align: center"]RM611266[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]BP wire[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM611111[/TD]

[TD="align: right"]5[/TD]
[TD="align: center"]RM611809[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Curl[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM611266[/TD]

[TD="align: right"]6[/TD]
[TD="align: center"]RM611807[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Class[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM611804[/TD]

[TD="align: right"]7[/TD]
[TD="align: center"]RM072229[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
[/TD]
[TD="align: center"]RM611266[/TD]

[TD="align: right"]8[/TD]
[TD="align: center"]RM611804[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]SARKUYSAN A.S[/TD]
[TD="align: center"]RM611263[/TD]

[TD="align: right"]9[/TD]
[TD="align: center"]RM611263[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: left"][/TD]
[TD="align: left"]SARKUYSAN A.S[/TD]
[TD="align: center"]RM611266[/TD]

</tbody>

****** id="cke_pastebin" style="position: absolute; top: 218px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

<tbody>
[TD="align: left"]Sarqusan[/TD]

</tbody>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Maybe something like this:
Excel Workbook
ABCDEFGHI
1MaterialVendor nameDescriptionVlookupVendor nameMaterialUsing INDEX - MATCH
2Form
3RM611266Anglia Metals LtdCableSarqusanSarqusanRM075005Sarqusan
4RM075005Anglia Metals LtdCrossySarqusanSarqusanRM611807Sarqusan
5RM604663Anglia Metals LtdPumpSarqusanRM61155
6RM611266Anglia Metals LtdBP wireSarqusanSarqusanRM611111Sarqusan
7RM611809Anglia Metals LtdCurlSarqusanRM611266
8RM611807Anglia Metals LtdClassSarqusanSarqusanRM611804Sarqusan
9RM072229Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)SarqusanRM611266
10RM611804Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)SarqusanSARKUYSAN A.SRM611263Sarqusan
11RM611263Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)SarqusanSARKUYSAN A.SRM611266SARKUYSAN A.S
Sheet
 
Upvote 0
Hi,

That's more than I expected. Thank you very much. What does the ISNA function do?
AhoyNC said:
Maybe something like this:

ABCDEFGHI

<colgroup><col style="width:30px; "><col style="width:114px;"><col style="width:132px;"><col style="width:226px;"><col style="width:154px;"><col style="width:35px;"><col style="width:24px;"><col style="width:117px;"><col style="width:89px;"><col style="width:193px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Material[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: left"]Vendor name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: left"]Description[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Vlookup[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: left"]Vendor name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Material[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Using INDEX - MATCH[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Form[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]RM611266[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cable[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM075005[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]RM075005[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Crossy[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM611807[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]RM604663[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Pump[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM61155[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]RM611266[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]BP wire[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM611111[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]RM611809[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Curl[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM611266[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]RM611807[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Class[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM611804[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]RM072229[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="align: left"]Sarqusan[/TD]
[TD="align: center"]RM611266[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "] [/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]RM611804[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]SARKUYSAN A.S[/TD]
[TD="align: center"]RM611263[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]RM611263[/TD]
[TD="align: left"]Anglia Metals Ltd[/TD]
[TD="align: left"]Cu wire 2,60mm (oxygen 250ppm)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Sarqusan[/TD]

[TD="align: left"]SARKUYSAN A.S[/TD]
[TD="align: center"]RM611266[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]SARKUYSAN A.S[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
D3=IF(ISNA(VLOOKUP(A3,$H$3:$H$11,1,0)),"","Sarqusan")
I3=IFERROR(INDEX($G$3:$G$11,MATCH(A3,$H$3:$H$11,0)),"")

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Sorry but wanted to go a bit further. If there is no match the field is empty. How could I make formula to say 'No match' instead of it being empty. Many thanks
 
Upvote 0
This will give the No Match.
If there is no match the formulas will give an error of #N/A. Used with the IF function the ISNA catches this error and will return "No Match".
Excel Workbook
ABCDEFGHI
1MaterialVendor nameDescriptionVlookupVendor nameMaterialUsing INDEX - MATCH
2Form
3RM611266Anglia Metals LtdCableSarqusanSarqusanRM075005Sarqusan
4RM075005Anglia Metals LtdCrossySarqusanSarqusanRM611807Sarqusan
5RM604663Anglia Metals LtdPumpNo MatchSarqusanRM61155No Match
6RM611266Anglia Metals LtdBP wireSarqusanSarqusanRM611111Sarqusan
7RM611809Anglia Metals LtdCurlNo MatchSarqusanRM611266No Match
8RM611807Anglia Metals LtdClassSarqusanSarqusanRM611804Sarqusan
9RM072229Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)No MatchSarqusanRM611266No Match
10RM611804Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)SarqusanSARKUYSAN A.SRM611263Sarqusan
11RM611263Anglia Metals LtdCu wire 2,60mm (oxygen 250ppm)SarqusanSARKUYSAN A.SRM611266SARKUYSAN A.S
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Concatenate column names from a pivot table
Replies
4
Views
17K
AusSteelMan
A

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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