IF TEXT FUNCTION

[Marc Wheeler]

Hi,

I would like F14 to State 'Yes' if either cells D14 or E14 contain 'TRUE'

D14 and E14 are both results from the following formula;

=IFNA(B14<>"","No")

=IFNA(C14<>"","No")

Which in turn following from cells B14 AND C14 which contain;

=VLOOKUP("*"&A14&"*",Home,1,FALSE)

=VLOOKUP("*"&A14&"*",Away,1,FALSE)

I'm trying to find a way for Excel to do an approximate vlookup for a name from a set of data and then if it is present in one of two table arrays when it states 'Yes'. So far I've had to go across multiple cells to get to this stage.

Picture attached should help;

Kind regards,

Marc

[#2 · Accepted solution]

Hi Marc,

If you need only Yes or No in result that could be like

=IF(IFNA(VLOOKUP("*"&A14&"*",Home,1,FALSE),"No")="No","No","Yes")

[#3]

Thank you - It worked perfectly.

Is there a way for the formula to check two different table arrays in the same formula?

[#4]

Marc, you may try

=IF(IFERROR(VLOOKUP("*"&A14&"*",Home,1,FALSE),IFNA(VLOOKUP("*"&A14&"*",Away,1,FALSE),"No"))="No","No","Yes")

[#5]

Thank you Sergei - It worked perfectly.

I don't suppose you'll be able to assist with the next issue I've run into?

I've been trying, and failing, to come up with a single formula that allows me to separate text data and move it into separate cells (which I've done over two cells at =left and right) --> These figures will then feed into an array of 5 cells of data which I need to turn into a fraction based on totals more than 2.5.

Example, if a team scores 3 home goals and concedes 1 then I want it to display 3 and 1 which then shows in another table with 4 previous goal figures. At this point we'll have 10 records of goals (5 home and 5 away) and I need it to show the % or fraction of how many matches had more than 2.5 goals.

At the moment I have all of the cells doing individual jobs -which is fine as I can just hide them in the background.

But what I would like is for the cells in column G and H to show a blank (or zero) cell if column E and F are 'N' or 'o' --> and to show the 'goals' if there is a number.

Current formulas;

Column D: =IFNA(VLOOKUP("*"&A11&"*",SCOREHOME,2,FALSE),IFNA(VLOOKUP("*"&A11&"*",SCOREAWAY,2,FALSE),"No"))

Column E: =LEFT(D11,1)

Column F: =RIGHT(D11,1)

Column G: =IF(E11="N", "0")

Column H: =IF(F11="N", "0")

(I need to show 0 or blank if 'N' or 'o' are in the cell and the number if not)

Column X: =IF(S11 > 2.5, 1, 0)