CODEWITHSUNDEEP
excel
learningbyexample
learningbyexample

Reputation: 1547

How would a excel formula be to check the range of cells if there is a value within a range and output the value of the row

output that value in this row

example:

    A    B    C    D    E
1  one  N/A  N/A  N/A  N/A
2  N/A  two  N/A  N/A  N/A
3  N/A  N/A  N/A   3   N/A
4  N/A  N/A  N/A  N/A   4

Now lets say that in the F column I want to have all the values that only have a value different than N/A

    A    B    C    D    E    F
1  one  N/A  N/A  N/A  N/A  one 
2  N/A  two  N/A  N/A  N/A  two
3  N/A  N/A  N/A   3   N/A   3
4  N/A  N/A  N/A  N/A   4    4

what I was thinking of doing was something like this don't know how to use the vlookup for this... don't know if this is the right course of action

=IF(ISNA(VLOOKUP(A1,Sheet1!$A:$A,1,false)),"N/A",""& value_here &"")

Upvotes: 1

Views: 41

Answers (1)

teylyn
teylyn

Reputation: 35915

Try this in F1 and copy down

=LOOKUP(2,1/(A1:E1<>"N/A"),A1:E1)

enter image description here

Edit: Use the Evaluate Formula tool to step through the formula and see how it works. First, the (A1:E1<>"N/A") is resolved into an array of False and one True values. Then that array is used as the divisor, which results in an array with many #Div/0! values for each False and numbers for each True.

Lookup is then performed, searching for a 2, which will find the position of the last number in the array, since all values are smaller than 1. The respective value of that array position is then returned.

Upvotes: 2

Related Questions