Table Match ?

[treeantz]

Hi
I have an unusual question, hoping someone could lend a helping hand.

I am trying to match an entry in a table based on two criteria which is Store and Status from a different table but can't really seem to figure out the correct formula.

To better illustrate here are two simplified tables.

Table A
Order # Store Status
123 ABC Paid
123 FGH Returned
123 XYZ Cancelled

Table B
Order # Store
123 ABC
123 FGH
123 XYZ

What I am hoping to achieve here is to return Status from Table A should there be a match in Table B.

As you can see Order # may be the same in different stores, I was able to use a vlookup but somehow it is only returning the first line of results.
My question is since the order can be the same but resides in a different store is it possible to have Excel to return the accurate result?

Example usage :
1) Return Status for Order# 123 if Store = XYZ
2) Return Status for Order# 123 if Store = ABC

Hope this explains my problem clearly.

It would be great if anyone can help me out.

Thanks in advance.

[Haseeb Avarakkan]

Hello treeantz,

Assume TableB contains in A1:B4, A1 & B1 are headings.

Try this ARRAY FORMULA (must hit CTRL+SHIFT+ENTER, rather than ENTER) in C2, then copy down.

=IFERROR(INDEX(TableAStatus,MATCH(1,IF(TableAOrder=A2,IF(TableAStore=B2,1)),0)),"")

[treeantz]

Hi Haseeb,

Thanks for reply.
Can I also ask what is TableAStatus, TableAOrder and TableAStore? I've never used index and match before.

It would be great if you could advise.

[Haseeb Avarakkan]

what is TableAStatus, TableAOrder and TableAStore?

I just used here for a reference, change all these with appropriate cell references in your table.

[treeantz]

Hi Haseeb, yes I finally got it.
Thank you very much for your assistance.

[treeantz]

btw do you know how i can change the thread to solved?

[ConneXionLost]

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save