IF AND formula help (Google Sheets)

Question

I'm working on a Fantasy Football stat sheet and running into a small hurdle. Happy to share example to sheet, but going to generalize the data here first to see if that will work.

I'm trying to use an IF AND formula (if that's even correct for this) and find out a value if both statements are true. For example (using 2 sheets/pages):

Sheet "Alpha" is shown below

Sheet "Beta":

E20 = John Smith
F20 = Rachel Smith

I am hoping to write a formula on SheetBeta. If E20 matches name for Row 1 AND F20 matches name from Column A, then return the value from where they intersect (which would be B4 in the example table below)

Sheet "Alpha":

	John Smith	Sarah Johnson	Rachel Smith
John Smith	100	125	150
Sarah Johnson	200	225	250
Rachel Smith	300	325	350

Thank you in advance!

player0 player0 · Accepted Answer · 2021-07-20T21:55:47

try:

=INDEX(IFNA(VLOOKUP(E20&"×"&F20, SPLIT(FLATTEN(
 IF(Alpha!B2:D="",,Alpha!A2:A&"×"&Alpha!B1:D1&"♀"&Alpha!B2:D)), "♀"), 2, 0)))

IF AND formula help (Google Sheets)

1 Answers