Formula required to get text

[vmohan1978]

All,
I required a formula in the attached document from cell C9 to C14 based on the value B9 to b14 , The formula should pick up answer from range B1 to D1 depending upon the values from B2 to D7 based on the meeting space.

Please find attached the file and do the needful.

Regards,

Mohan

 

[shrivallabha]

Please see if below formula works for you, implement this in cell C9 and copy down as much needed:

=INDEX($A$1:$D$1,1,MATCH(B9,INDEX($A$1:$D$7,MATCH(A9,$A$1:$A$7,0),0),0))

Note: I have tested it in Office 365 and I am little unsure if it will work the same in other versions of Excel. Post back if it doesn't.

 

[vmohan1978]

Hi, Sorry for the late response.
Formula working correctly if i want to match approximate value then how can i modify it.

 

[shrivallabha]

Hi, Sorry for the late response.
Formula working correctly if i want to match approximate value then how can i modify it.

Thanks for informing. What exactly will be outcome of approximate matching? Can you explain with few examples?

 

[vmohan1978]

Now i getting the error #N/A. I think you had applied the formula considering the exact match of value like(30,50,20) in the case of Conf. room if the value is other than 30,50,20.
Suppose if the value is 22, I should get nearest to the given range is 20 then i should Team-Based Work / High Collaboration.
Suppose if the value is 28, I should get nearest to the given range is 30 then i should Balanced Work / Collaboration.

Hope i explained properly.

 

[shrivallabha]

Okay, in such case you could use below construct with the dataset posted at #1 which will check for minimum possible difference between reference values and locate.

=INDEX($A$1:$D$1,1,MATCH(MIN(ABS(B9-INDEX($B$1:$D$7,MATCH(A9,$A$1:$A$7,0),0))),ABS(B9-INDEX($B$1:$D$7,MATCH(A9,$A$1:$A$7,0),0)),0)+1)

Note that this will fetch the first match (from the first encountered column) in case of boundary condition i.e. you have 20, 30 in dataset and the entered value is 25.