I have excel1 and excel2 I need to do vlookup and this is the formula for vlookup
=VLOOKUP(AZ2,‘[mis3418_M3dsb_report_detail_wkly_ILC_BL_Detail_17&03-2024.xlsx]Sheet1’$H:$V,15,0]
Here AZ2 is the column name of excel1, mis3418_M3dsb_report_detail_wkly_ILC_BL_Detail_17&03-2024.xlsx]Sheet1’ is the excel2 name H is column name and V is also column name of excel2.
Can anyone please help me
The VLOOKUP formula looks good! Here’s a quick rundown:
- Ensure the Excel2 file path (in single quotes) and column index (desired data location) are accurate.
- Use absolute references ($AZ2) when copying the formula.
- Consider XLOOKUP for more flexibility (if your Excel version supports it).
These adjustments should help you with the VLOOKUP!
But I want a linq for this
@0bb4628e217fd43ac86ac9294,
Share DataTable columns or excel file for this to write linq query.
Thanks,
Ashok ![:slight_smile: :slight_smile:](https://emoji.discourse-cdn.com/twitter/slight_smile.png?v=12)
Get all data into data table, read below details
Absolutely, here’s the LINQ equivalent for your VLOOKUP scenario:
`C#var lookupValue = AZ2; // Assuming AZ2 holds the lookup value from Excel1
// Load data from Excel2 (replace with your actual data loading method)
var excel2Data = LoadExcel2Data(); // This function should return a collection representing Excel2 data
// Perform the lookup using LINQ
var result = excel2Data
.Where(row => row[/* Column index for lookup in Excel2 */] == lookupValue)
.FirstOrDefault(); // Returns the first matching row
// Access desired data from the result row (assuming column index 14 for desired data)
if (result != null)
{
var desiredData = result[14]; // Adjust the index based on your actual column position (H is column 8, so 14 for index)
}`
content_copy
Explanation:
- Lookup Value: We store the value from
AZ2
in a variable lookupValue
.
- Load Excel2 Data: Replace
LoadExcel2Data()
with your actual method to load data from Excel2. This function should return a collection of objects representing each row in Excel2.
- LINQ Query:
.Where
: Filters the excel2Data
collection based on the lookup value. It checks if the value in the specified column index (replace with the actual index) of each row matches the lookupValue
.
.FirstOrDefault
: Returns the first matching row from the filtered data. It returns null
if no match is found.
- Access Data:
- If a match is found (
result != null
), we access the desired data from the matching row using its index (modify 14
to the actual column index for your desired data).
Note:
- This example assumes you know the column indexes in Excel2 for lookup and desired data retrieval.
- Modify the column indexes based on your actual sheet structure.
- Error handling for data loading or missing matches might be necessary in a real-world scenario.