- 551K Members
- 5,139 Online
- 660K Conversations

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

- Home
- :
- Excel
- :
- General Discussion
- :
- Re: 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

Highlighted

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

Highlighted

- 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.

Highlighted

- 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?

Highlighted

- 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.

Highlighted

- 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

SharePoint 2013 LIst Filters (by latest date)

dmphil
in
SharePoint
on
02-27-2020
34
Views

0 Likes

0 Replies

Outlook Message box GUI blacked out when sending attachment through Excel

midohioboarder
in
Office 365
on
01-24-2020
254
Views

0 Likes

0 Replies

Pivot Table StDev calculates different value then the STDEV formula

zsoltturkosi
in
Excel
on
10-21-2019
217
Views

0 Likes

4 Replies

PWA fullscreen like IE11 kiosk mode

rogihee
in
Discussions
on
10-19-2019
469
Views

0 Likes

5 Replies

Trying to add multiple users to distribution group and getting error: Cannot validate argument

Test SharePoint
in
Office 365
on
10-12-2019
910
Views

0 Likes

4 Replies

need macro to find and copy range of data between specified cells.

spike3rd
in
Office 365
on
10-09-2019
166
Views

0 Likes

0 Replies

Share

Popular

Learning Resources

Programs

Values

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