Vlookup data1

Help Activities
1

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

2

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!

3

But I want a linq for this

4

@0bb4628e217fd43ac86ac9294,

Share DataTable columns or excel file for this to write linq query.

Thanks,
Ashok :slight_smile:

5

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:

  1. Lookup Value: We store the value from AZ2 in a variable lookupValue.
  2. 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.
  3. 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.
  1. 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.