Data search across multiple Excel sheets
OBEPINE
-
didibonf Posted messages 418 Registration date Status Member Last intervention -
didibonf Posted messages 418 Registration date Status Member Last intervention -
Hello CCM Community,
I’m facing the following problem:
I have an Excel file with 4 sheets (Entreesav, Suivisav, BOSE and Alpines).
Entreesav is a table which, through a few manipulations, allows me to create unique records reported to my Suivisav sheet.
Suivisav is somewhat a recap of the Entreesav sheet, but I would like to also report information that can be either on my BOSE sheet or on Alpine.
Indeed, knowing that all gray cells must not be modified because they are either filled in manually or already automated, I would like that starting from my SAV Depot No. and my Serial No., the tables fill automatically in both directions.
Important information: The SAV Depot No. is a unique number, but the Serial No. can appear several times in my tables because I can receive it multiple times for repair.
Here is the folder link: https://www.cjoint.com/c/IAhqvw1tGVW
I can’t find the right formulas, I’ve tried many VLOOKUP, HLOOKUP, XLOOKUP, but no solution...
Could you please help me?
Thank you all.
I’m facing the following problem:
I have an Excel file with 4 sheets (Entreesav, Suivisav, BOSE and Alpines).
Entreesav is a table which, through a few manipulations, allows me to create unique records reported to my Suivisav sheet.
Suivisav is somewhat a recap of the Entreesav sheet, but I would like to also report information that can be either on my BOSE sheet or on Alpine.
Indeed, knowing that all gray cells must not be modified because they are either filled in manually or already automated, I would like that starting from my SAV Depot No. and my Serial No., the tables fill automatically in both directions.
Important information: The SAV Depot No. is a unique number, but the Serial No. can appear several times in my tables because I can receive it multiple times for repair.
Here is the folder link: https://www.cjoint.com/c/IAhqvw1tGVW
I can’t find the right formulas, I’ve tried many VLOOKUP, HLOOKUP, XLOOKUP, but no solution...
Could you please help me?
Thank you all.
1 answer
Hello,
I think the problem comes from the VLOOKUP functions used.
For example in the BOSE sheet in H2 you have:
=IF(A2="";"";VLOOKUP(I2;SAV;5;FALSE))
The VLOOKUP function looks up the value I2 in the first column of the SAV data and returns the value from the fifth column. But I2 corresponds to a serial number and not the depot number. Try: =IF(A2="";"";VLOOKUP(A2;SAV;5;FALSE))
I think the problem comes from the VLOOKUP functions used.
For example in the BOSE sheet in H2 you have:
=IF(A2="";"";VLOOKUP(I2;SAV;5;FALSE))
The VLOOKUP function looks up the value I2 in the first column of the SAV data and returns the value from the fifth column. But I2 corresponds to a serial number and not the depot number. Try: =IF(A2="";"";VLOOKUP(A2;SAV;5;FALSE))
The problem is that I cannot search by the deposit number because a deposit number can correspond to several serial numbers...
=IF(A2="";"";INDEX(SAV;MATCH(BOSE!$I2;Suivisav!$F:$F;0);5))