Closest search function

[SC16]

Hi,

New to this forum so sorry if this has been asked before.

I am thinking about trying to write a formula/function that you can use to find what title best matches the search criteria from a list.

For example, if I had a table looking like this:
James Jack John Jim Jeremy
Money 8 1 6 4 3
Sales 10 20 40 25 40
Call Backs 1 3 3 4 1
Assets 30 10 20 30 20


And I wanted to create a table that I could input various values (IE see below) and see which candidate most closely matches my inputs:
Money 7
Sales 25
Call Backs 3
Assets 20

Then based on the formula in the excel cell I could get an answer telling me that "John" most closely matches all the criteria I inputted.

I am thinking it would require an Index Formula with an internal Match function to identify which cell is needed, yet I am unsure the best way to work this out and then summate who most closely matches based on ALL the criteria.

Could anyone help?

I hope this makes sense, thanks.

[tim201110]

=index($a$1:$f$1,aggregate(14,6,column($b$1:$f$1)*(i2=abs(b2:f2-h2))*(i3=abs(b3:f3-h3))*(i4=abs(b4:f4-h4))*(i5=abs(b5:f5-h5)),1))

[SC16]

Tim,

Thanks for your response.

Having a playaround with that, it seems that it only works when the inputs are 'close' to the original numbers. If I try a table with values
6
10
5
29

Then it doesn't seem to work? Why is this?


Also, trying to get my head around the formula. You create an arrray and then index it through the array to get the response?

[FlameRetired]

Hello SC16. Welcome to the forum.

Try array entering this formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

It also returns "John" with those new numbers which I believe is correct. What name are you expecting?

	A	B	C	D	E	F	G	H	I	J
1		James	Jack	John	Jim	Jeremy
2	Money	8	1	6	4	3		Money	7	John
3	Sales	10	20	40	25	40		Sales	25
4	Call Backs	1	3	3	4	1		Call Backs	3
5	Assets	30	10	20	30	20		Assets	20

And with new numbers

	A	B	C	D	E	F	G	H	I	J
1		James	Jack	John	Jim	Jeremy
2	Money	8	1	6	4	3		Money	6	John
3	Sales	10	20	40	25	40		Sales	10
4	Call Backs	1	3	3	4	1		Call Backs	3
5	Assets	30	10	20	30	20		Assets	20

[FlameRetired]

My mistake. The second set of numbers returns "James" but could just as easily return "John". They both have same qualification ... a tie.

	A	B	C	D	E	F	G	H	I	J
1		James	Jack	John	Jim	Jeremy
2	Money	8	1	6	4	3		Money	6	James
3	Sales	10	20	40	25	40		Sales	10
4	Call Backs	1	3	3	4	1		Call Backs	5
5	Assets	30	10	20	30	20		Assets	29