SOLVED

# Return value based on criteria

Brass Contributor

# Return value based on criteria

Hi,

I need a formula that will find a matching value and return a record

Example

E6 = "SPA"

F6 = "3.23"

E13 = "SPA"

F13 = "3.23"

The formula will go into F13 and return "3.23"

For more on this see attached file:

Find cells with value.xlsx

7 Replies

# Re: Return value based on criteria

@packie
formula present in attached file
hope this will help.

# Re: Return value based on criteria

Thank you for responding

Just nearly what I am looking for. It needs a little modifying though.

Where there is nothing to return then the cell should remain empty showing neither "0" or "not found"

best response confirmed by packie (Brass Contributor)
Solution

# Re: Return value based on criteria

@packie
As per your comment I have modified this file. Attaching the same

# Re: Return value based on criteria

Wonderful. Thank you.

# Re: Return value based on criteria

@packie
Thank you for accepting solution provided by me.

# Re: Return value based on criteria

As variant

``=LET(x, XLOOKUP(\$E\$13:E23, \$E\$6:\$E\$12,\$F\$6:\$F\$12,""), IF(x="", "", x) )``

# Re: Return value based on criteria

Or bit another setup

``=SUBSTITUTE( XLOOKUP(\$E\$6:\$E\$200, \$E\$6:\$E\$200, \$F\$6:\$F\$200, ""), "", "" )``
1 best response

Accepted Solutions
best response confirmed by packie (Brass Contributor)
Solution

# Re: Return value based on criteria

@packie
As per your comment I have modified this file. Attaching the same