INDEX / MATCH using multiple tables depending on values?

[nostrum]

Hi Guys,

I have a formula I am unable to crack, hoping you can help. Attached is an example, hopefully self-explanatory but heres the run down.

Depending on Data in C5 & C7 decides which table to use. I have used CONCATENATE as I was looking to use the INDIRECT function to point to the direction of the table required?

Once we have the right table I need to use a Vlookup or INDEX /MATCH type of function to match both axis of the table to the data in C9 and C11 and match the data in the table!

Hope you can help and thanks in advance!

[Bo_Ry]

Please try at C14
=INDEX(H:N,AGGREGATE(15,6,ROW(G5:G73)/(G5:G73=C9)/(ROW(G5:G73)>MATCH(C5&" "&C7,G:G,)),1),MATCH(C11,H5:N5))

Data Validation for C9
=OFFSET($G$1,MATCH(C5&" "&C7,G:G,),,AGGREGATE(15,6,ROW($G$5:$G$74)/($G$5:$G$74=0)/(ROW($G$5:$G$74)>MATCH(C5&" "&C7,G:G,)),1)-MATCH(C5&" "&C7,G:G,)-1)

[José Augusto]

Hi

@Bo_Ry has a great solution to get the value. My solution is worse.

Please Login or Register  to view this content.

On the other hand, it provided data validation whose formula is exaggerated

Please Login or Register  to view this content.

[nostrum]

Hi Guys,

Thanks for the help here, much appreciated.

Just to clarify The only formula required is for C14, the other boxes will have been filled from another source already. I tried the first formaula in C14 but it has a NUM! error!

Much appreciated

[nostrum]

Just to add for clarification, The answer to my example would be 29 from cell L55.

This is because it is from table B1800 (B coming from C5 and 1800 coming from C7) and row 55 coming from C9 (closest to 1.6 being 1.5) and L resulting from a direct match from C11 (10)

Hope that makes sense?

[José Augusto]

Hi

I maintained your data validation for C9, and implemented 2 formulas and adapted the @Bo_Ry solution.
The second formula takes into account that the headers of zone B are general and take line 45 as the default for the search. In the first formula was taken the line referring to the search of group 'B 1800' in line 51.

See the formulas and the file

Please Login or Register  to view this content.

[nostrum]

Hi Jose,

Thanks very much for the help!

Unfortunately, it just doesn't seem to be doing what I'd have hoped

Not sure where it's going wrong, just some of the results don't match

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Bo_Ry]

@José Thanks, but I think my and your formula doesn't check for closest value.

row 55 coming from C9 (closest to 1.6 being 1.5) and L resulting from a direct match from C11 (10)

if C9 = 2 then it close to both 1.5 and 2.5

This one choose 2.5
=INDEX(H:N,MOD(MIN(INDEX(ABS(INDEX(G:G,MATCH(C5&" "&C7,G:G,)+1):INDEX(G:G,AGGREGATE(15,6,ROW(G5:G74)/(G5:G74=0)/(ROW(G5:G74)>MATCH(C5&" "&C7,G:G,)),1)-1)-C9)*10^5+ROW(INDEX(G:G,MATCH(C5&" "&C7,G:G,)+1):INDEX(G:G,AGGREGATE(15,6,ROW(G5:G74)/(G5:G74=0)/(ROW(G5:G74)>MATCH(C5&" "&C7,G:G,)),1)-1)),)),1000),C11-5)


This one choose 1.5
=INDEX(H:N,-MOD(MIN(INDEX(ABS(INDEX(G:G,MATCH(C5&" "&C7,G:G,)+1):INDEX(G:G,AGGREGATE(15,6,ROW(G5:G74)/(G5:G74=0)/(ROW(G5:G74)>MATCH(C5&" "&C7,G:G,)),1)-1)-C9)*10^5-ROW(INDEX(G:G,MATCH(C5&" "&C7,G:G,)+1):INDEX(G:G,AGGREGATE(15,6,ROW(G5:G74)/(G5:G74=0)/(ROW(G5:G74)>MATCH(C5&" "&C7,G:G,)),1)-1)),)),-1000),MATCH(C11,H5:H5:N5))

[nostrum]

Hi,

Sorry for the rule break, does the title suit the topic now?

[AliGW]

Yes - much better, thank you.

[José Augusto]

Hi

The approach I use to catch that is use the same formulas but with a rounded value of C9, accordingly with A or B

Please Login or Register  to view this content.

Result_1, Result_2 and Result_3

Please Login or Register  to view this content.

See the file

[nostrum]

Thanks for the help guys,

Bo_Ry appears to have cracked it. Sorry for the delay responding I have only just managed to give it another try!

Much appreciated