Newbie in need of help!: Formula on a dynamic cell which moves

Hi, firstly thank you for reading, and a mega thank you for your input.

I have an integration into our CRM system and Excel pulls in information, record at a time.

I have a second sheet which pulls in this information from the 1st [Input] sheet and calculates some pricing.

Sometimes, when the data is pulled in, the order is not always absolute and sometimes changes.
Here is an example of one of my formulas on the pricing sheet:

=Input!BC3

So the problem is, the cell (which is titled "Customer Discount" above BC3) sometimes moves to cell BD3.

im looking for a way of saying =look for "Customer Discount" and take the value from the cell underneath it. Is this possible in excel?


Many Many thanks!

Hi, here is an example workbook.

Where on sheet 1, [Input] sometimes when the data is imported, the Discount column (both rows) can end up in J1 and J2.

The formulas on the second sheet gets the data from the Input sheet.

Many thanks!


example sheet.xlsx

Hello benishiryo,

thank you for your recommendation, I will give it a go!

Kind regards, Sugar James.

Ive just tried the HLOOKUP and it works perfectly, thank you!