HLOOKUP to get exact number formatting?

Hi.
I got big table with various metrics, where all of them are generally 4 types:
Currency
Number
Percentage
Text

i seen no problem then Hlookup delivers me currency, but in other cases, sometimes I need to get the output in the same number format as the source in table:

if the data in source table row is percentage, i need to have an output as percentages.
if the data in source table row is general number, I need to have an output as general number.
If the data in source table row is currency, I need to have an output as currency.

At the moment, if I have a table with outputs and will put general number formatting, i can have the result, where instead of 16% I will see 0.16.

the problem is, that I cannot pre-set the formatting for the output cells, because users will define the type of number format in the table later. for example, for one department, which will use my file, it might be that lets say line 5 in table will have data as currency. For other department, the line 5 might be % of anything and they will show percentages in the same line 5.

if there any way I could have the formatting dependable on the source formatting?
File attached
Book43.xlsx

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

are the entries governed by the kpi?

well it could probably done with a load of address/indirects and ifs

=IF(CELL("format",INDIRECT(ADDRESS(4,MATCH(Sheet1!$B$1,Sheet2!$A$1:$P$1,0),,,"sheet2")))="C0",TEXT(HLOOKUP($B$1,Sheet2!$D$1:$P$25,9,FALSE),"£#,##0k;(£#,##0k)"),if(..............
but you'd probably be beter of trying to get someone to code it in vba

Try this in d4 filled down(note it uses the value of the kpi in col b to get the correct cell)

Formula:

Please Login or Register  to view this content.