dynamic formula

[PRASHANT RAWAT]

Hello team,

I need the solution in attached sheet.

As I have the four table which contains the employee's details like the product, date of joining month, Designation and employee Id and the basis of the tables i need the result through names instead of the employee id.

Note:- Please design the dynamic formula so that we can change the header as well. Don’t change the sequence of the questioning table. I mean no arranging allowed of employee details.

[#2]

Hello,

start by looking up the Employee ID in cell E13

=INDEX(Empl,MATCH(B13,$B$3:$B$8,0))

This will require unique names. If there are several people named "Amit" in the Employee table, the formula will only return the first matching ID.

Based on the result of cell E13 you can then look up the data in the other tables, for example Product:

=INDEX($E$3:$E$8,MATCH(E13,$D$3:$D$8,0))

Create similar formulas with the first range of the Index pointing to the column where the desired value is, i.e. Designation, Date of joining, and the second parameter of the Match function should point to the column with the Employee ID in the respective table. 

Let me know if that helps.

[#3]

It is not working in the sheet

[#4]

Q1. Please lookup the values by employee name in desired table and design dynamic formula so that we can change the Header.
Don’t change the sequence of questioning table.

[#5 · Accepted solution]

"It's not working" is not good enough. Do you get an error or a wrong result? Have you applied the formulas correctly? 

I have not changed column order or renamed any columns and it's working perfectly fine for me with the approach I posted above.

E13 =INDEX(Empl,MATCH(B13,$B$3:$B$8,0))

C13 =INDEX($E$3:$E$8,MATCH($E13,$D$3:$D$8,0))

D13 =INDEX($H$3:$H$8,MATCH($E13,$G$3:$G$8,0))

F13 =INDEX($H$3:$H$8,MATCH($E13,$G$3:$G$8,0))

Copy row 13 formulas down. See attached file.

If that is not working for you, please post your workbook with the formulas applied.