Multi VLookup, with an if condition

[Alaa_Ali]

Hello

I need a help with vlookup & if condition

I have a workbook contains multiple sheets "12 sheets"

The sheet is designed to put the code in "code column" then vlookup the product name and the price from other sheets.

So What I want to do is to make an equation that vlookup in warehouse sheet first for the name "column C" and the price "column G" if the equation did not find anything returns back to search in the same sheet for the name and KG price "the last column in every sheet"

for example, I want to vlookup the code "BP0001" highlighted in red in sheet "W - F"
The vlookup returns NA because it's not found in "warehouse"
I want then to search in the sheet "W - F" when it returns NA search in the other sheets until the equation returns value

I hope what I want is clear

and thanks in advance

 

[Alaa_Ali]

I can't upload the file in the forum

So I uploaded it in google drive

https://drive.google.com/file/d/1dYHL4rpGFbPR3reVJDDC2KF7os2tMJTD/view?usp=sharing

 

[Alaa_Ali]

I have uploaded the file to the forum

 

[bosco_yip]

I have uploaded the file to the forum

Try,

1] Define name >>

Name : ShtsExcldThs

Refer to : =SUBSTITUTE(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW()),REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),""),0)

2] In "W-F" sheet E6, array formula (CTRL+SHIFT+ENTER) copy down :

=IFERROR(VLOOKUP(B6,Warehouse!B:G,2,0),IFERROR(VLOOKUP(B6,INDIRECT("'"&INDEX(ShtsExcldThs,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtsExcldThs&"'!$B$5:$J$100"),B6)>0),0))&"'!$B$5:$J$100"),MATCH("Product",$B$4:$J$4,0),0),""))

3] In "W-F" sheet G6, array formula (CTRL+SHIFT+ENTER) copy down :

=IFERROR(VLOOKUP(B6,Warehouse!B:G,6,0),IFERROR(VLOOKUP(B6,INDIRECT("'"&INDEX(ShtsExcldThs,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtsExcldThs&"'!$B$5:$J$100"),B6)>0),0))&"'!$B$5:$J$100"),MATCH("KG price",$B$4:$J$4,0),0),""))

4] The other worksheets formulas same as the above [2] and [3]

p.s. Since the define name formula used a macro-4 function, the file will be saved in a macro-enable worksheet .xlsm file

Regards
Bosco

 

[Alaa_Ali]

Thanks, man, it works perfectly

can I ask you to explain what you did with that formula and that macro

 

[bosco_yip]

Thanks, man, it works perfectly

can I ask you to explain what you did with that formula and that macro

1] The defined name: "ShtsExcldThs", GET.WORKBOOK(1) is a macro 4 function, the purpose is to return the names of Sheets List exclude the current list.

2] The 2nd part of VLOOKUP () is a formula to lookup values across multiple worksheets

......VLOOKUP(B6,INDIRECT("'"&INDEX(ShtsExcldThs,MATCH(1,--(COUNTIF(INDIRECT("'"&ShtsExcldThs&"'!$B$5:$J$100"),B6)>0),0))&"'!$B$5:$J$100"),MATCH("Product",$B$4:$J$4,0),0)

details as per below link :

https://www.extendoffice.com/documents/excel/2450-excel-vlookup-in-multiple-sheets.html

Regards
Bosco