SOLVED
Home

I think I need a If Then formula

%3CLINGO-SUB%20id%3D%22lingo-sub-309157%22%20slang%3D%22en-US%22%3EI%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309157%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20and%20thank%20you%20for%20looking.%20I%20can't%20search%20because%20my%20description%20is%20moderately%20vague.%26nbsp%3B%20I%20want%20a%20formula%20that%20looks%20to%20see%20if%20a%20cell%20matches%20a%20value%20in%20a%20column%20and%20would%20then%20pull%20a%20value%20from%20another%20corresponding%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei.e.%20there%20are%20a%20list%20of%20100%20numbers%20in%20column%20A%20and%20B.%20I%20want%20it%20to%20look%20at%20the%20number%20I%20enter%20in%20C1%20and%20if%20it%20finds%20that%20number%20in%20column%20A%2C%20it%20shows%20the%20corresponding%20number%20in%20column%20B.%20So%20if%20I%20enter%2060%20in%20C1%2C%20it%20would%20search%20column%20A%20for%2060%20%2Cand%20let's%20say%20it%20finds%20it%20in%20A30%20it%20would%20then%20show%20the%20number%20in%20B30.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20explanation%20is%20really%20convoluted%2C%20so%20please%20let%20me%20know%20if%20anyone%20can%20help%2C%20or%20needs%20more%20clarification.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Eformula%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETutorial%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310340%22%20slang%3D%22en-US%22%3ERe%3A%20I%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310340%22%20slang%3D%22en-US%22%3E%3CP%3EYour%20welcome.%20Don't%20hesitate%20to%20send%20me%20a%20message%20if%20you%20need%20a%20hand%20with%20anything%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-310265%22%20slang%3D%22en-US%22%3ERe%3A%20I%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-310265%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20is%20amazing.%20You%20are%20a%20giant%20among%20mortals!%20Thank%20you%20for%20all%20your%20help%2C%20and%20taking%20the%20time%20for%20the%20detailed%20explanation.%20I%20am%20much%20more%20capable%20now%20thanks%20to%20your%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309873%22%20slang%3D%22en-US%22%3ERe%3A%20I%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309873%22%20slang%3D%22en-US%22%3E%3CP%3EYour%20welcome.%20Sure.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20syntax%20for%20Vlookup%20is%20Vlookup('Value%20to%20Search%20For'%2C'Range%20to%20Search%20In'%2C'Column%20Number%20to%20Return%20Values%20From'%2C'True%2FFalse').%20Each%20of%20the%20parameters%20that%20we%20are%20inputting%20into%20the%20function%20is%20called%20an%20argument%20(Ie.%20this%20function%20has%204%20arguments)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20case%20we%20are%20searching%20for%20the%20value%20in%20C1%2C%20so%20C1%20is%20our%20first%20argument%20for%20the%20function%20(Do%20you%20understand%20absolute%20vs.%20relative%20cell%20references%3F%20That's%20what%20the%20dollar%20signs%20are%20for%2C%20If%20we'd%20written%26nbsp%3B%3CSPAN%3E%3DVLOOKUP(C1%2CA1%3AB100%2C2%2CFALSE)%20and%20copied%20the%20formula%20down%20to%20the%20next%20row%20it%20would%20change%20to%26nbsp%3B%3DVLOOKUP(C2%2CA2%3AB101%2C2%2CFALSE)%2C%20by%20using%20the%20%24%20we%20lock%20the%20formula%20and%20it%20would%20remain%20the%20same%20as%20we%20copy%20down%20(Or%20across)%2C%20If%20you%20had%20a%20value%20in%20C2%2C%20C3%2C%20etc%20that%20you%20wanted%20to%20lookup%20and%20wanted%20to%20be%20able%20to%20copy%20the%20formula%20down%20than%20we%20could%20remove%20the%20%24%20from%20the%20C1%20reference%20(So%26nbsp%3B%3DVLOOKUP(C1%2C%24A%241%3A%24B%24100%2C2%2CFALSE)%2C%20would%20then%20read%20%3DVLOOKUP(C2%2C%24A%241%3A%24B%24100%2C2%2Cfalse)%20when%20copied%20down%20to%20the%20next%20row.%20Google%26nbsp%3Babsolute%20vs.%20relative%20cell%20references%20for%20more%20reading%20on%20this%2C%20very%20handy%20when%20you%20are%20copying%20a%20formula%20across%20multiple%20cells.)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20next%20argument%20is%20the%20range%20we%20want%20to%20search%20in%20including%20the%20range%20we%20want%20to%20return%20values%20from%20(A1%3AB100%20in%20this%20case%2C%20if%20the%20value%20we%20wanted%20to%20return%20was%20in%20column%20C%2C%20then%20it%20would%20be%20A1%3AC100)%2C%20alternately%20you%20could%20search%20the%20entire%20row%20(so%20A%3AB%20for%20the%20second%20argument)%20as%20opposed%20to%20limiting%20this%20to%20row%201%20to%20100.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%203rd%20argument%20is%20the%20column%20number%20that%20we%20want%20to%20return%20values%20from%2C%20B%20being%20the%202nd%26nbsp%3Bcolumn%20in%20the%20range%20that%20is%20our%202nd%20argument%20(A1%3AB100).%20The%20first%20argument%20(C1)%20will%20always%20be%20searched%20for%20in%20the%20first%20column%20of%20the%20range%20that%20is%20the%202nd%20argument%20(A1%3AB100)%2C%20Vlookup%20can%20only%20go%20from%20left%20to%20right%20(Ie.%20we%20couldn't%20search%20for%20a%20value%20in%20B%20and%20return%20a%20value%20in%20A).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20final%20argument%20simply%20indicates%20whether%20a%20partial%20match%20would%20be%20returned%20(False%20means%20exact%20match%2C%20True%20means%20partial%20match)%2C%20typically%20a%20partial%20match%20would%20only%20be%20used%20if%20our%20data%20was%20sorted.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EDoes%20that%20help%20at%20all%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309681%22%20slang%3D%22en-US%22%3ERe%3A%20I%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309681%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20did%20exactly%20it.%20Thank%20you%20so%20much!%20Could%20you%20explain%20how%20you%20set%20that%20up%2C%20or%20point%20me%20to%20a%20link%20where%20I%20can%20learn%20it%3F%20That%20will%20be%20incredibly%20useful%20for%20a%20few%20things%20I%20have%20coming%20up%2C%20so%20I%20would%20like%20to%20understand%20it%20rather%20than%20just%20cut%20and%20paste.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309158%22%20slang%3D%22en-US%22%3ERe%3A%20I%20think%20I%20need%20a%20If%20Then%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309158%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Assuming%20I%20understand%20you%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24C%241%2C%24A%241%3A%24B%24100%2C2%2CFALSE)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
shawndwyer
New Contributor

Hello, and thank you for looking. I can't search because my description is moderately vague.  I want a formula that looks to see if a cell matches a value in a column and would then pull a value from another corresponding column.

 

i.e. there are a list of 100 numbers in column A and B. I want it to look at the number I enter in C1 and if it finds that number in column A, it shows the corresponding number in column B. So if I enter 60 in C1, it would search column A for 60 ,and let's say it finds it in A30 it would then show the number in B30.

 

My explanation is really convoluted, so please let me know if anyone can help, or needs more clarification.

5 Replies
Solution

Hi, Assuming I understand you correctly.

 

=VLOOKUP($C$1,$A$1:$B$100,2,FALSE)

That did exactly it. Thank you so much! Could you explain how you set that up, or point me to a link where I can learn it? That will be incredibly useful for a few things I have coming up, so I would like to understand it rather than just cut and paste. 

Your welcome. Sure.

 

The syntax for Vlookup is Vlookup('Value to Search For','Range to Search In','Column Number to Return Values From','True/False'). Each of the parameters that we are inputting into the function is called an argument (Ie. this function has 4 arguments)

 

In your case we are searching for the value in C1, so C1 is our first argument for the function (Do you understand absolute vs. relative cell references? That's what the dollar signs are for, If we'd written =VLOOKUP(C1,A1:B100,2,FALSE) and copied the formula down to the next row it would change to =VLOOKUP(C2,A2:B101,2,FALSE), by using the $ we lock the formula and it would remain the same as we copy down (Or across), If you had a value in C2, C3, etc that you wanted to lookup and wanted to be able to copy the formula down than we could remove the $ from the C1 reference (So =VLOOKUP(C1,$A$1:$B$100,2,FALSE), would then read =VLOOKUP(C2,$A$1:$B$100,2,false) when copied down to the next row. Google absolute vs. relative cell references for more reading on this, very handy when you are copying a formula across multiple cells.)

 

The next argument is the range we want to search in including the range we want to return values from (A1:B100 in this case, if the value we wanted to return was in column C, then it would be A1:C100), alternately you could search the entire row (so A:B for the second argument) as opposed to limiting this to row 1 to 100.

 

The 3rd argument is the column number that we want to return values from, B being the 2nd column in the range that is our 2nd argument (A1:B100). The first argument (C1) will always be searched for in the first column of the range that is the 2nd argument (A1:B100), Vlookup can only go from left to right (Ie. we couldn't search for a value in B and return a value in A).

 

The final argument simply indicates whether a partial match would be returned (False means exact match, True means partial match), typically a partial match would only be used if our data was sorted. 

 

Does that help at all?

 

 

 

 

That is amazing. You are a giant among mortals! Thank you for all your help, and taking the time for the detailed explanation. I am much more capable now thanks to your assistance.

Your welcome. Don't hesitate to send me a message if you need a hand with anything else.

Related Conversations
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies