I have an array....
{eve, 20; adam, 60; helen, 80; paris, 5}
I would like to search the values of THAT array, with the values of THIS array....
{eve; eve; helen; adam; helen}
to produce this...
{20; 20; 80; 60; 80}
In sheets, the first array is in the range A1:B4, and the second is D1:D5
My initial attempt looked like this ....
=ARRAYFORMULA(if(A1:A4 = D1:D5, A1:B4, ""))
This obviously doesn't work, and I've been wracking my brains for the past 5 hours trying different tricks like placing $ signs in different range values, and things like...
=ARRAYFORMULA(if(A1:A4 =indirect("D"&sequence(count(d1:d5))), A1:B4, ""))
I'm fairly new to this, so please for the love of all that is holy, release me from this living nightmare, and I will be forever grateful
Formula for your array:
=arrayformula(VLOOKUP({"eve"; "eve"; "helen"; "adam"; "helen"},{"eve", 20; "adam", 60; "helen", 80; "paris", 5},2,false))
Formula for cell range:
=arrayformula(VLOOKUP(D1:D5, A1:B4, 2, false))