Formula to Display Cell Value/Text

[jhudson444]

Hi. I am trying to create a formula that would display the value or text of a cell based on criteria in another cell.

Ex.

...A...B
..34..xl
..45....

In the above table, whenever "xl" occurs in column B, the formula should display the value of the adjacent cell in column A, which would be "34".

Is this even possible?

[noboffinme]

Hi jhudson444,

How about the below?

=IF(B2="xl",A2,"")

[jhudson444]

Thanks, that works.

Now, if possible, is there a way apply that formula to a range of cells?

Something like: =IF(B2:B3="xl",A2:A3," ")

The problem with the above formula is that it will only work if it is placed in a cell that is adjacent to the cells that it is trying to calculate. If placed in other parts of the sheet, it will not work.

[Ghozi Alkatiri]

try this one
=SUMPRODUCT((B2:B3="xl")*(A2:A3))

[jhudson444]

That works!

The formula that you created will add up values in column A every time there is "xl" in the adjacent cell in column B.

Is there any way to only display the most-recent value in column A when there is "xl" in column B?

Ex.

...A.....B
..34....xl
..45....xl
..24....xl

The formula should result in 24.

[Ghozi Alkatiri]

may be like this
=INDEX($A$2:$A$4,MATCH("xl",$B$2:$B$4))

[jhudson444]

Thanks!

That formula only looks for "xl". Is there a way to make it look for "xl" or "xb"?

Ex.

...A.....B
..34....xb
..45....xl
..24....xb

The result is still 24 even though there is "xb" next to it.