CODEWITHSUNDEEP
excelsearchif-statement
user3866570
user3866570

Reputation: 1

using if search formula in excel

is there a way to combine a search formula with a lookup or possibly use an if then statement. I think I have the first part working but need help with the second part. I am looking for values in column A and based on the values found in column A, I need to look at values in other columns and return the value found in that column. Example, if column A contains value "car", I need to look at column B and return the value found in column B in column F , if column A contain value "boat", I need to look at column C and return the value found in column C in column F. Any help would be much appreciated

Upvotes: 0

Views: 131

Answers (3)

Floris
Floris

Reputation: 46375

Assuming that you have a list of possible values: car, boat, bike, plane, ... that would lead you to look for values in column B, C, D, E, ... I suggest you do the following:

  1. Define the name "transportation" (whatever name you want to call it) with the types of transportation you need - for example, ={"car", "bike", "tram", "bus", "boat"} . On Office for Mac 2011 you do this with Insert->Name->Define... - for other versions of Excel it might be different (but note - you actually type both the = sign and the {} curly braces around the list of values you want to be able to look up). Put them in the order of the columns that you want.

  2. For every lookup that you need, you can now write

    =INDEX(B4:F4,0,MATCH(A4,transportation,0))

if the value (car, bus etc) is in cell A4, and you want to look up the corresponding value from columns B through F.

If you have column headings above your columns, you can use the MATCH function without having to define a name explicitly. For example, if you have car, bike, tram etc in cells B1:F1, you can use

=INDEX(B4:F4, 0, MATCH(A4, $B$1:$F$1, 0))

to do the lookup.

Explanation: the MATCH function (with third parameter 0) looks for the exact match for the first value (in cell A4 in this case) in the array that is the second parameter (either the named range, or the range with fixed address that I gave above). You then look up the appropriate cell using the INDEX function which gives you an offset into the range (B4:B4 in the above) of cells where you need to do the lookup.

I trust you can adapt this to your exact needs. Ask if you need more help.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166316

=IF(A1="car",B1,IF(A1="boat",C1,""))

Upvotes: 0

NWinocur
NWinocur

Reputation: 74

The following formula is the simplest way I can think to do it: In cell F1, the formula would be

=IF(A1="car",B1,C1)

This is case insensitive, so it should work for CAR as well. But bear in mind that this has the downside that with a formula this simple, ANY value in A1 other than "car" (not just "boat") would lead to the other value ending up in the F column.

Upvotes: 0

Related Questions