If and VLookup multiple tables

[Armourgeddon1]

Hi all - have been trying to write an IF with VLOOKUP nested statement.

I would like to look at the first table where the PICK column is negative (TABLE 1) and vlookup the next table to see if stock is available.(TABLE 2). Then reverse the process later from table 2 to table 1.

I have over 9000 lines to search on both tables

Can anyone help getting multiple issues !! and eating many head-ache tablets

Sample of tables below

Hope some-one can help my sanity!!

Item	Locator	Quantity	MIN A	PICK						Item	Locator	Quantity	MIN C	PICK
80001007	EXPENSEA.B26.04.	1	0	1						80001012	EXPENSEC.CKS65..	2	1	1
80001007	EXPENSEA.SP37.001.001	3	0	3						80001013	EXPENSEC.CKS65..	2	1	1
80001008	EXPENSEA.SP37.001.001	2	0	2						80001020	EXPENSEC.CKS-CHEMICAL..	8	2	6
80001009	EXPENSEA.B26..	4	0	4						80001021	EXPENSEC.CKS48.006.02A	1	1	0
80001012	EXPENSEA.B26.01.	1	1	0						80001033	EXPENSEC.CKS52.003.02A	1	0	1		TABLE 2
80001013	EXPENSEA.B26.01.	1	1	0		TABLE 1				80001034	EXPENSEC.CKS52.002.01A	2	0	2
80001014	EXPENSEA.B26.02.	1	0	1						80001037	EXPENSEC.CKS52.002.01C	1	0	1

<tbody>
</tbody>

 

[Muhammad_Usman]

Re: Help with If and VLookup multiple tables

Hey Armourgeddon1 , Please also share you out put table for batter understanding.

 

[Armourgeddon1]

Re: Help with If and VLookup multiple tables

Hi sorry new to this how do i attach the file?

 

[Fluff]

Re: Help with If and VLookup multiple tables

You don't need to attach a file, just show a mock-up of you output in the same way as you showed your table in post#1

 

[Armourgeddon1]

Re: Help with If and VLookup multiple tables

oh no bother

Item	Locator	Quantity	MIN A	PICK						Item	Locator	Quantity	MIN C	PICK
80001007	EXPENSEA.B26.04.	1	0	1						80001012	EXPENSEC.CKS65..	2	1	1
80001007	EXPENSEA.SP37.001.001	3	0	3						80001013	EXPENSEC.CKS65..	2	1	1
80001008	EXPENSEA.SP37.001.001	2	0	2						80001020	EXPENSEC.CKS-CHEMICAL..	8	2	6
80001009	EXPENSEA.B26..	4	0	4						80001021	EXPENSEC.CKS48.006.02A	1	1	0
80001012	EXPENSEA.B26.01.	1	1	0						80001033	EXPENSEC.CKS52.003.02A	1	0	1
80001013	EXPENSEA.B26.01.	1	1	0						80001034	EXPENSEC.CKS52.002.01A	2	0	2
80001014	EXPENSEA.B26.02.	1	0	1						80001037	EXPENSEC.CKS52.002.01C	1	0	1
80001015	EXPENSEA.SP-CHEMICAL..	50	20	30						80001038	EXPENSEC.CKS45.001.01C	1	1	0
80001016	EXPENSEA.B24..	45	20	25						80001039	EXPENSEC.CKS52.002.01E	1	1	0
80001020	EXPENSEA.SP-CHEMICAL..	2	2	0						80001049	EXPENSEC.CKS52.002.02E	3	0	3
80001021	EXPENSEA.SP15.003.01C	1	1	0						80001053	EXPENSEC.CKS52.002.03E	4	0	4
80001024	EXPENSEA.SP-QX7..	6	1	5						80001061	EXPENSEC.CKS52.002.01D	11	0	11
80001025	EXPENSEA.B26.02.	1	0	1						80001062	EXPENSEC.CKS52.002.02A	3	1	2
80001026	EXPENSEA.B26.01.	2	0	2						80001065	EXPENSEC.CKS54.002.01A	2	2	0
80001027	EXPENSEA.B26..	2	0	2						80001068	EXPENSEC.CKS96..	2	0	2
80001028	EXPENSEA.B26..	1	0	1						80001069	EXPENSEC.CKS-CHEMICAL..	1	1	0
80001029	EXPENSEA.SP33.004.02B	1	0	1						80001070	EXPENSEC.CKS68..	25	5	20
80001030	EXPENSEA.B25.08.	2	0	2						80001071	EXPENSEC.CKS68..	22	5	17
80001031	EXPENSEA.SP20.004.01A	3	0	3						80001072	EXPENSEC.CKS52.002.02B	3	1	2
80001032	EXPENSEA.SP-QX7..	1	0	1						80001076	EXPENSEC.CKS54.002.01B	20	2	18
80001035	EXPENSEA.SP08.004.003	2	0	2						80001077	EXPENSEC.CKS61.003.01A	1	0	1
80001037	EXPENSEA.SP07.003.01B	1	1	0						80001079	EXPENSEC.CKS52.002.02C	3	0	3
80001038	EXPENSEA.SP12.003.02C	1	0	1						80001080	EXPENSEC.CKS52.002.03C	2	1	1
80001039	EXPENSEA.SP15.003.03B	3	0	3						80001082	EXPENSEC.CKS52.002.03A	2	1	1
80001040	EXPENSEA.SP09.002.02C	2	1	1						80001083	EXPENSEC.CKS61.003.01B	2	1	1
80001041	EXPENSEA.SP34.003.01L	6	3	3						80001084	EXPENSEC.CKS65..	1	0	1
80001042	EXPENSEA.SP34.003.01M	5	2	3						80001120	EXPENSEC.CKS52.002.02D	2	0	2
80001043	EXPENSEA.SP34.001.01P	6	1	5						80001121	EXPENSEC.CKS52.002.03D	4	3	1
80001044	EXPENSEA.SP34.002.01J	1	0	1						80001122	EXPENSEC.CKS61.001.01B	2	1	1
80001046	EXPENSEA.SP22.002.01C	9	1	8						80001123	EXPENSEC.CKS52.003.01E	1	1	0
80001047	EXPENSEA.SP10.003.001	2	1	1						80001124	EXPENSEC.CKS61.001.01C	3	1	2
80001050	EXPENSEA.SP10.001.02A	83	4	79						80001128	EXPENSEC.CKS54.002.02B	1	2	-1
80001051	EXPENSEA.SP34.006.03A	5	2	3						80001129	EXPENSEC.CKS52.003.02E	5	2	3
80001052	EXPENSEA.SP10.001.01A	18	10	8						80001130	EXPENSEC.CKS54.001.01D	2	1	1
80001054	EXPENSEA.SP13.003.01M	4	1	3						80001131	EXPENSEC.CKS61.001.01D	2	1	1
80001055	EXPENSEA.SP11.004.01L	2	1	1						80001132	EXPENSEC.CKS61.001.01E	1	0	1
80001056	EXPENSEA.SP08.003.03A	7	4	3						80001134	EXPENSEC.CKS52.003.03E	2	1	1
80001059	EXPENSEA.SP18.003.04A	2	0	2						80001135	EXPENSEC.CKS52.003.02B	9	2	7
80001062	EXPENSEA.SP10.001.02D	2	1	1						80001138	EXPENSEC.CKS61.003.01C	4	2	2
80001065	EXPENSEA.SP20.004.02A	3	1	2						80001140	EXPENSEC.CKS49.005.01A	1	1	0
80001069	EXPENSEA.SP-CHEMICAL..	2	1	1						80001143	EXPENSEC.CKS52.003.01A	1	1	0
80001070	EXPENSEA.SP48.004.001	15	15	0						80001146	EXPENSEC.CKS47.004.03E	2	1	1
80001071	EXPENSEA.SP48.001.001	14	13	1						80001147	EXPENSEC.CKS61.001.02B	4	1	3
80001072	EXPENSEA.SP01.004.01B	3	1	2						80001148	EXPENSEC.CKS52.003.01B	1	1	0
80001076	EXPENSEA.SP25.003.01B	16	8	8						80001149	EXPENSEC.CKS52.003.01C	1	0	1
80001077	EXPENSEA.SP27.001.01C	1	0	1						80001150	EXPENSEC.CKS61.001.02C	2	1	1
80001078	EXPENSEA.SP09.001.01A	1	1	0						80001151	EXPENSEC.CKS138..	2	1	1
80001079	EXPENSEA.SP02.003.01B	1	0	1						80001154	EXPENSEC.CKS52.003.01D	3	1	2
80001080	EXPENSEA.SP34.006.03A	2	1	1						80001156	EXPENSEC.CKS61.001.02D	2	1	1
80001082	EXPENSEA.SP12.002.03B	2	1	1						80001157	EXPENSEC.CKS54.001.01B	1	0	1
80001083	EXPENSEA.SP23.004.01C	3	1	2						80001158	EXPENSEC.CKS54.001.01C	1	0	1
80001084	EXPENSEA.SP19.003.1A	2	0	2						80001159	EXPENSEC.CKS54.002.02C	2	1	1
80001090	EXPENSEA.SP01.001.001	2	0	2						80001160	EXPENSEC.CKS52.003.03A	5	2	3
80001095	EXPENSEA.SP22.004.01A	4	2	2						80001162	EXPENSEC.CKS52.003.03B	4	2	2
80001096	EXPENSEA.SP28.001.01B	1	0	1						80001165	EXPENSEC.CKS54.003.01A	1	0	1
80001097	EXPENSEA.SP28.001.01C	2	0	2						80001166	EXPENSEC.CKS83..	1	1	0
80001098	EXPENSEA.SP29.001.02A	2	0	2						80001168	EXPENSEC.CKS54.003.01C	1	1	0
80001099	EXPENSEA.SP28.001.01A	27	0	27						80001170	EXPENSEC.CKS52.003.02C	1	0	1
80001100	EXPENSEA.SP35..00101A	1	0	1						80001171	EXPENSEC.CKS54.003.01D	5	0	5
80001101	EXPENSEA.SP35..00101A	1	0	1						80001180	EXPENSEC.CKS65..	1	0	1
80001102	EXPENSEA.SP17.003.01A	1	0	1						80001185	EXPENSEC.CKS48.006.01A	1	1	0
80001103	EXPENSEA.SP-CHEMICAL..	2	0	2						80001187	EXPENSEC.CKS-CHEMICAL..	2	5	-3
80001120	EXPENSEA.SP-ARC.11.004	18	0	18						80001188	EXPENSEC.CKS65..	1	0	1
80001121	EXPENSEA.SP09.002.03A	3	2	1						80001190	EXPENSEC.CKS53.001.01B	8	6	2
80001124	EXPENSEA.SP23.004.03B	3	1	2						80001191	EXPENSEC.CKS54.004.01A	2	0	2
80001125	EXPENSEA.B26.05.	2	0	2						80001192	EXPENSEC.CKS54.004.01B	1	1	0
80001128	EXPENSEA.SP05.002.05B	1	0	1						80001193	EXPENSEC.CKS54.004.01C	1	0	1
80001129	EXPENSEA.B25.12.	21	0	21						80001194	EXPENSEC.CKS54.004.02A	1	1	0
80001130	EXPENSEA.SP03.001.04B	3	0	3						80001195	EXPENSEC.CKS61.001.02E	2	1	1
80001132	EXPENSEA.B25.08.	2	1	1						80001196	EXPENSEC.CKS50.001.01A	10	2	8
80001133	EXPENSEA.SP05.002.05A	2	0	2						80001197	EXPENSEC.CKS53.001.01C	2	0	2
80001134	EXPENSEA.B25.13.	1	0	1						80001203	EXPENSEC.CKS61.003.02A	2	0	2
80001135	EXPENSEA.SP02.003.03B	8	4	4						80001204	EXPENSEC.CKS54.004.02B	1	1	0
80001139	EXPENSEA.SP04.001.01A	3	0	3						80001205	EXPENSEC.CKS54.004.02C	3	1	2
80001141	EXPENSEA.SP-QX7..	3	1	2						80001206	EXPENSEC.CKS54.005.01A	2	1	1
80001147	EXPENSEA.B25.13.	73	1	72						80001207	EXPENSEC.CKS124..	2	0	2
80001148	EXPENSEA.SP-QX7..	3	1	2						80001208	EXPENSEC.CKS54.005.01B	1	0	1
80001149	EXPENSEA.SP05.002.04A	2	1	1						80001213	EXPENSEC.CKS54.005.01C	3	1	2
80001154	EXPENSEA.SP05.004.05C	19	1	18						80001214	EXPENSEC.CKS52.004.01D	3	1	2
80001157	EXPENSEA.SP08.002.03A	1	0	1						80001215	EXPENSEC.CKS54.005.01D	2	1	1
80001158	EXPENSEA.SP10.003.03B	1	0	1						80001216	EXPENSEC.CKS54.005.02A	1	1	0
80001159	EXPENSEA.SP05.003.03B	3	1	2						80001217	EXPENSEC.CKS55.004.01A	2	0	2
80001161	EXPENSEA.SP09.004.002	1	1	0						80001218	EXPENSEC.CKS45.008.02B	1	0	1
80001162	EXPENSEA.B25.05.	4	2	2						80001219	EXPENSEC.CKS56.007.01A	3	0	3

<colgroup><col><col><col span="2"><col><col span="5"><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>