How to create an excel dropdown list that displays text with hidden vlookups values?

Hi there!

(working in gamedev as an CSR and analyst)
Please see attachment.

I'm trying to create a "map" in Excel in the cells H26:P43.
All the cells on the map have a dropdown menu created using the sheet "data for map"

Now i would like to link the textvalue from the cell with a multiple hidden vlookup formula that impacts the data on H23:P23

Ie. in cell L33 im choosing "Janine3" so it changes the values for I23, K23, N23 and P23 to
'Data for map'!G32
'Data for map'!H32
'Data for map'!I32
'Data for map'!J32

So it will show 45, 6, 35, 65

but if i add in cell J27 "farm 3" it adds the values from G17:J17 to the values I23, K23, N23 and P23.
'Data for map'!G32 + 'Data for map'!G17
'Data for map'!H32 + 'Data for map'!H17
'Data for map'!I32 + 'Data for map'!I17
'Data for map'!J32 + 'Data for map'!J17

So it will SUM and show 82, 9, 35, 105

Many thanks. Im trying to do it since yesterday...

1 cell in the grey shaded area = 1entry (from the column A in the Data for map sheet) but the entry should take in account the values (Col G-j in the data for map sheet) for that entry.

Yes and Yes

I know... I tried to do another sheet with tables using IfError Vlookups but it didnt work. Many thanks for your help Glenn!

All columns H26-P43 have drop down selection lists but we are only returning values for the 4 headings in row 23.

If the drop downs were limited to columns H , J, M and O AND only one selection per row, we could return the results in rows 26_43 (I, K. N, P)and simply SUM in row 23.

Is this possible?

Originally Posted by Glenn Kennedy

OK. Here it is, as requested.

HOLY CRAP! Many thanks Glenn, you're a life saver! I knew it had to do something with the IFERROR formula. You've done something i was trying to create for at least 8 hours now.

BTW: "There is a more hardcore version of this issue. My boss asked me to take in account the SIZE ("Data for map" sheet column F ie 3x3) of the buildings.
So for example choosing from the dropdown in the "map sheet" in the grey area a 3x3 will merge cells around to make one big cell taking 3 rows and 3 columns!
I told him that im 99% sure its not possible..."