Extract/pull data to columns based on 2 criteria

[Lucky_Jackie]

Greetings,

I am seeking an advice related to Microsoft excel 2010,

My tables are shown in the kindly attached photo below,

What is needed, in Table_3,

To populate column [AMOUNT] with values from Table_2 which match Employees Names for a selected month;

To populate the columns [SNO], [EMPLOYEE NAME], [ACC NO.] & [BANK] with values from Table_1 which match a selected option "Internet Banking" in column [PAYMENT METHOD] of Table_1 and bank name selected in Table_3.

(Noted that column [EMPLOYEE NAME] of Table_2 values are referenced from column [EMPLOYEE NAME] of Table_1)

I have tried several formulas related to Index&Match however the columns are being populated with the first match only,

I am trying to automate the process as much as possible. I have spent several days in learning excel however unfortunately I was not successful,

What is the best approach to achieve this that is compatible with Office 2010?

Many thanks in advance.

 

[pecoflyer]

Please post a sample sheet, not a picture. Thx

 

[Monty]

Greetings,

I am seeking an advice related to Microsoft excel 2010,

My tables are shown in the kindly attached photo below,

What is needed, in Table_3,

To populate column [AMOUNT] with values from Table_2 which match Employees Names for a selected month;

To populate the columns [SNO], [EMPLOYEE NAME], [ACC NO.] & [BANK] with values from Table_1 which match a selected option "Internet Banking" in column [PAYMENT METHOD] of Table_1 and bank name selected in Table_3.

(Noted that column [EMPLOYEE NAME] of Table_2 values are referenced from column [EMPLOYEE NAME] of Table_1)

I have tried several formulas related to Index&Match however the columns are being populated with the first match only,

I am trying to automate the process as much as possible. I have spent several days in learning excel however unfortunately I was not successful,

What is the best approach to achieve this that is compatible with Office 2010?

Many thanks in advance.

Hello Lucky
To achieve this in Microsoft Excel 2010, you can use a combination of `VLOOKUP` and `INDEX-MATCH` functions. Let's break down the steps for each part of your requirement:

1. Populate [AMOUNT] in Table_3:
- Assuming your employee names are in column A of both Table_2 and Table_3, and the month you want to match is in cell F1, you can use the following formula in Table_3, cell C2 (assuming headers are in row 1):

=VLOOKUP($A2 & $F$1, Table_2[#All], 2, FALSE)

This formula combines the employee name and the selected month and looks up the corresponding amount in Table_2.

2.Populate [SNO], [EMPLOYEE NAME], [ACC NO.], and [BANK] in Table_3:
- Assuming your employee names are in column A of Table_1 and the payment method is in column E, and the bank name is in cell D1 in Table_3, you can use the following formulas in Table_3:

[SNO] (assuming headers are in row 1):

=INDEX(Table_1[#All],MATCH($A2,Table_1[EMPLOYEE NAME],0),1)

[EMPLOYEE NAME]:
=$A2

[ACC NO.]:

=VLOOKUP($A2,Table_1[#All],MATCH("ACC NO.",Table_1[#Headers],0),FALSE

[BANK]:
=D$1


These formulas use `INDEX-MATCH` and `VLOOKUP` to retrieve the relevant information from Table_1 based on the selected payment method and bank name.

Make sure to adjust cell references and table/column names based on your actual data structure. This should help automate the process as you've described.