INDEX vs MATCH??

Question

Dearest Wizards,

Sheet 1:

A11-60 has the names of the states

B5-E5 has the values; Tom, Mary, Harry, Jane

B11-E60 has the amount of money each person spent while visiting the associated state

Sheet 2:

A11# is a VSTACK (by the 4 individuals)/FILTERED (to exclude any states that no one has visited) collection of STATE NAMES.  (A state may show up more than once or not at all.)

B11# is a VSTACK (by the 4 individuals)/FILTERED (to exclude any states that no one has visited) collection of the AMOUNT SPENT in the state while visited by one of the individuals.

What formula do I use in Sheet 2 Col C# to show the name of the individual associated with the values in col A & B

TIA for your help with this.

Sam

Answer 1

You should be able to derive the person by matching each spilled State/Amount pair back to Sheet 1 and returning the column header where that match occurs. Assuming state + amount uniquely identify the visit, put this in Sheet 2 C11 (it will spill):

=MAP(A11#,B11#,LAMBDA(s,a,
  XLOOKUP(
    1,
    (Sheet1!$A$11:$A$60=s)*
    (Sheet1!$B$11:$E$60=a),
    Sheet1!$B$5:$E$5
  )
))

For each row of the spill, this looks for the row where the state matches s, the column where the amount equals a, and returns the corresponding person name from B5:E5.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin