Problem with index and match with two array value

[TIGERNOSE]

Hi!
My lecture gave me a task.
I need to match the "Price" with 3 criteria in different tables
I've been stuck for a week.
Maybe this forum can help me
HELP.png

[Pete_UK]

It would help if you attached a sample Excel workbook, rather than a picture of one - see the yellow banner at the top of the screen for guidance on how to do this.

Your third table seems to contain only AGENT as the source, so your formula in D2 could be arranged along the lines of:

=IF(B2="AGENT","Use table in K:N","Use table in F:I")

Do you have to use INDEX/MATCH? You could use SUMIFS instead.

As this is clearly an assignment from your lecturer, I won't give you the exact answer, but that should get you along the way.

Hope this helps.

Pete

[TIGERNOSE]

Thanks for helping me to solve the task.
I have uploaded the workbook.
Can you show me how it looks with sumif formula?

There are many agents in the tables, i just show one agent.

[Pete_UK]

You can use this formula in D3:

=IF(B3="AGENT",SUMIFS(N:N,K:K,A3,M:M,C3),SUMIFS(I:I,F:F,A3,G:G,B3,H:H,C3))

then copy down as required.

Hope this helps.

Pete

[TIGERNOSE]

Thanks Pete, I already use your formula. It works.
But, what if the source from the table 3 have another "AGENT" i mean, something like "AGENT WASHINGTON", "AGENT TOKYO", and another hundred of different source agent.
It's a lot of work if i type all of different source.
i hope there is another alternatif formula

[bebo021999]

Try:

Please Login or Register  to view this content.

[JohnTopley]

Look at how you might "extract" the text agent from a text such as "AGENT WASHINGTON" and "AGENT TOKYO"

=IF(B3="contains the text AGENT",SUMIFS(N:N,K:K,A3,M:M,C3),SUMIFS(I:I,F:F,A3,G:G,B3,H:H,C3))


"contains the text AGENT" needs to be replaced by a formula: HINT look at LEFT, RIGHT, MID functions

[TIGERNOSE]

Thank you so much bebo021999, your formula works like a charm.

[TIGERNOSE]

Okay, now i know what do you mean. It works more specific and i will use your formula instead. Thanks for helping me to solve the task

[Pete_UK]

Your sample data should be representative of your real data, showing the variations that you might encounter, not just one AGENT.

You can use this type of construct:

=IF(Source_in_B3 is in Table2,"use Table2","use Table3")

and to determine if the "Source_in_B3 is in Table2" you can use:

COUNTIF(G:G,B3)

Can you put it all together from that?

Pete