Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- 466K Members
- 8,747 Online
- 563K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- I think I need a If Then formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

SOLVED
## I think I need a If Then formula

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

shawndwyer

New Contributor

01-04-2019
01:04 PM
- last edited on
07-12-2019
11:18 AM
by
TechCommunityAP

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2019
01:04 PM
- last edited on
07-12-2019
11:18 AM
by
TechCommunityAP

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.

Labels:

5 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-04-2019 01:07 PM

SolutionHi, Assuming I understand you correctly.

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

Best Response confirmed by
shawndwyer (New Contributor)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019 04:47 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-07-2019 10:35 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2019 06:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2019 08:40 AM

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

Related Conversations

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
13.5K
Views

2 Likes

9 Replies

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
35.6K
Views

7 Likes

35 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
182K
Views

8 Likes

30 Replies

Security Community Webinars

Valon_Kolica
in
Security, Privacy & Compliance
on
10-22-2019
16.8K
Views

12 Likes

13 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft