Formula to Auto populate the fields in the table

[Rameez Sarang]

Hi,
I am working on the attached sheet and looking for a formula or solution which would auto populate the values in the table once I select the Carrier Global description.


I have attached the sheet for reference.

Appreciate you help on this.

 

[vletm]

One sample ...

 

[Rameez Sarang]

Thank you for the attached sheet.

However, please can you onfirm what does (3 & 7) stands for in the address formula that you have used. Which rows do the exactly refer to.

=MATCH(C$27,INDIRECT(ADDRESS(3,MATCH($B30,$1:$1,0)-1) & ":" & ADDRESS(7,MATCH($B30,$1:$1,0)-1)),0)

 

[vletm]

Rameez Sarang​

You can use the ADDRESS function to obtain the address of a cell in a worksheet, given specified row and column numbers.
For example, ADDRESS(2,3) returns $C$2. As another example, ADDRESS(77,300) returns $KN$77.

Syntax​

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num Required. A numeric value that specifies the row number to use in the cell reference.
• column_num Required. A numeric value that specifies the column number to use in the cell reference.

 

[vletm]

Rameez Sarang​

Instead of ADDRESS-function, You could also use something like in this sample.
Or
You could combine those two tables to one and use one lookup-value (number or text).
... You can do that Yourself.

 

[Rameez Sarang]

many thanks for your help on this. I am able to populate the fields with the solution you suggested.

Appreciate all your help on this