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

[toony]

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

	A	B	C	D	E	F	G	H
	Material	Vendor name	Description	Vlookup Form			Vendor name	Material
1	RM611266	Anglia Metals Ltd	Cable				Sarqusan	RM075005
2	RM075005	Anglia Metals Ltd	Crossy				Sarqusan <tbody> </tbody>	RM611807
3	RM604663	Anglia Metals Ltd	Pump				Sarqusan <tbody> </tbody>	RM61155
4	RM611266	Anglia Metals Ltd	BP wire				Sarqusan <tbody> </tbody>	RM611111
5	RM611809	Anglia Metals Ltd	Curl				Sarqusan <tbody> </tbody>	RM611266
6	RM611807	Anglia Metals Ltd	Class				Sarqusan <tbody> </tbody>	RM611804
7	RM072229	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)				Sarqusan <tbody> </tbody>	RM611266
8	RM611804	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)				SARKUYSAN A.S	RM611263
9	RM611263	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)				SARKUYSAN A.S	RM611266

<tbody>
</tbody>

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

Sarqusan

<tbody>
</tbody>

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Material	Vendor name	Description	Vlookup			Vendor name	Material	Using INDEX - MATCH
2				Form
3	RM611266	Anglia Metals Ltd	Cable	Sarqusan			Sarqusan	RM075005	Sarqusan
4	RM075005	Anglia Metals Ltd	Crossy	Sarqusan			Sarqusan	RM611807	Sarqusan
5	RM604663	Anglia Metals Ltd	Pump				Sarqusan	RM61155
6	RM611266	Anglia Metals Ltd	BP wire	Sarqusan			Sarqusan	RM611111	Sarqusan
7	RM611809	Anglia Metals Ltd	Curl				Sarqusan	RM611266
8	RM611807	Anglia Metals Ltd	Class	Sarqusan			Sarqusan	RM611804	Sarqusan
9	RM072229	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)				Sarqusan	RM611266
10	RM611804	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611263	Sarqusan
11	RM611263	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611266	SARKUYSAN A.S
Sheet

 

[toony]

Hi,

That's more than I expected. Thank you very much. What does the ISNA function do?

Maybe something like this:

	A	B	C	D	E	F	G	H	I
1	Material	Vendor name	Description	Vlookup			Vendor name	Material	Using INDEX - MATCH
2	Form
3	RM611266	Anglia Metals Ltd	Cable	Sarqusan			Sarqusan	RM075005	Sarqusan
4	RM075005	Anglia Metals Ltd	Crossy	Sarqusan			Sarqusan	RM611807	Sarqusan
5	RM604663	Anglia Metals Ltd	Pump				Sarqusan	RM61155
6	RM611266	Anglia Metals Ltd	BP wire	Sarqusan			Sarqusan	RM611111	Sarqusan
7	RM611809	Anglia Metals Ltd	Curl				Sarqusan	RM611266
8	RM611807	Anglia Metals Ltd	Class	Sarqusan			Sarqusan	RM611804	Sarqusan
9	RM072229	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)				Sarqusan	RM611266
10	RM611804	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611263	Sarqusan
11	RM611263	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611266	SARKUYSAN A.S

<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>
</tbody>

Spreadsheet Formulas

Cell Formula 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

 

[toony]

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

 

[AhoyNC]

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
	A	B	C	D	E	F	G	H	I
1	Material	Vendor name	Description	Vlookup			Vendor name	Material	Using INDEX - MATCH
2				Form
3	RM611266	Anglia Metals Ltd	Cable	Sarqusan			Sarqusan	RM075005	Sarqusan
4	RM075005	Anglia Metals Ltd	Crossy	Sarqusan			Sarqusan	RM611807	Sarqusan
5	RM604663	Anglia Metals Ltd	Pump	No Match			Sarqusan	RM61155	No Match
6	RM611266	Anglia Metals Ltd	BP wire	Sarqusan			Sarqusan	RM611111	Sarqusan
7	RM611809	Anglia Metals Ltd	Curl	No Match			Sarqusan	RM611266	No Match
8	RM611807	Anglia Metals Ltd	Class	Sarqusan			Sarqusan	RM611804	Sarqusan
9	RM072229	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	No Match			Sarqusan	RM611266	No Match
10	RM611804	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611263	Sarqusan
11	RM611263	Anglia Metals Ltd	Cu wire 2,60mm (oxygen 250ppm)	Sarqusan			SARKUYSAN A.S	RM611266	SARKUYSAN A.S
Sheet