Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have three columns on a spreadsheet. The first two columns I need sorted so that it includes everything 80% and greater. The third column I also need 80% or greater but this column is a result of a VLOOKUP formula.
Solved by M. H. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 16/05/2018 - 11:21
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
Excelchat Expert 16/05/2018 - 11:22
I understand that you need help in sorting your data. May you please share the document you have so I can help you with your concern?
User 16/05/2018 - 11:22
Well, the document is huge with multiple tabs. Not something easily shared
User 16/05/2018 - 11:23
I have two columns and need to include everything that is greater than 80%
User 16/05/2018 - 11:23
in both coulmns
User 16/05/2018 - 11:23
columns
Excelchat Expert 16/05/2018 - 11:23
Alright, can you share a screenshot so I would have an idea on how your data looks like?
User 16/05/2018 - 11:23
Yes
Excelchat Expert 16/05/2018 - 11:23
Or you can share just the tabs that you need help with..
Excelchat Expert 16/05/2018 - 11:24
You can paste the values here if the file has formulas
User 16/05/2018 - 11:25
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"--",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
User 16/05/2018 - 11:25
That formula results in the last column giving me another percent
User 16/05/2018 - 11:25
percentage
User 16/05/2018 - 11:26
I need something that will give me a report listing all three columns that have values greater than 80%
Excelchat Expert 16/05/2018 - 11:26
Ok just to verify, you need to filter out everything that is greater than 80% for all 3 columns correct?
User 16/05/2018 - 11:27
I need everything that is greater than 80%
User 16/05/2018 - 11:27
80 or greater
Excelchat Expert 16/05/2018 - 11:27
in any of the 3 columns?
User 16/05/2018 - 11:27
yes
User 16/05/2018 - 11:28
if one column has 80 and another has 82, that row needs to be displayed
User 16/05/2018 - 11:28
if all three have it, then that row needs to be displayed
User 16/05/2018 - 11:29
if only one has it, then that row needs to be displayed
Excelchat Expert 16/05/2018 - 11:29
ok got it
User 16/05/2018 - 11:30
That's right
Excelchat Expert 16/05/2018 - 11:30
ok here's the formula that you can use: =IF(OR(B5>80%,C5>80%,D5>80%),"YES","")
User 16/05/2018 - 11:30
like that
Excelchat Expert 16/05/2018 - 11:30
just change the cell reference to the correct cell reference you have on your data
User 16/05/2018 - 11:30
where do I put that? Do I highlight all the rows and columns
Excelchat Expert 16/05/2018 - 11:31
on the last available column you have, first row with data then drag it to the last row
User 16/05/2018 - 11:31
ok
Excelchat Expert 16/05/2018 - 11:32
Then afterwards, you can filter all of your data, and show only those with YES in the column you have added
User 16/05/2018 - 11:32
Can I change it to =>
User 16/05/2018 - 11:32
or just put 79
Excelchat Expert 16/05/2018 - 11:33
so you also need to include 80%?
Excelchat Expert 16/05/2018 - 11:33
Do you mean greater than or equal to 80%?
Excelchat Expert 16/05/2018 - 11:34
If greater than or equal to 80% then yes you can use >79%
Excelchat Expert 16/05/2018 - 11:34
=IF(OR(B5>79%,C5>79%,D5>79%),"YES","")
Excelchat Expert 16/05/2018 - 11:34
Do you have any questions regarding the solution provided?
User 16/05/2018 - 11:34
But that last column has that big formula
Excelchat Expert 16/05/2018 - 11:35
how many columns you have right now? 3?
User 16/05/2018 - 11:35
yes
Excelchat Expert 16/05/2018 - 11:35
Then add a new column to add the formula and name it FILTER
User 16/05/2018 - 11:35
ok
Excelchat Expert 16/05/2018 - 11:36
Same with what's showing on the document preview
User 16/05/2018 - 11:36
ok4
Excelchat Expert 16/05/2018 - 11:36
Did it work?
User 16/05/2018 - 11:36
Checking
Excelchat Expert 16/05/2018 - 11:38
Ok, let me know if you are satisfied with the solution provided.
Excelchat Expert 16/05/2018 - 11:43
Hi, did it work on your end?
User 16/05/2018 - 11:44
Perfect!
Excelchat Expert 16/05/2018 - 11:44
Awesome!
Excelchat Expert 16/05/2018 - 11:45
Is there anything else that I can assist you with regarding this issue?
User 16/05/2018 - 11:45
Hold on one sec
User 16/05/2018 - 11:47
I am good!
User 16/05/2018 - 11:47
Thanks!
Excelchat Expert 16/05/2018 - 11:47
Perfect! Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
Excelchat Expert 16/05/2018 - 11:50
Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
User 16/05/2018 - 11:52
Help!
User 16/05/2018 - 11:52
I need to exclude dashes!
User 16/05/2018 - 11:52
in the last column
Excelchat Expert 16/05/2018 - 11:53
what is the result showing in column 4?
Excelchat Expert 16/05/2018 - 11:53
for those with dashes? shouldn't it be BLANK not YES?
User 16/05/2018 - 11:54
It is showing YES when there is a dash and the other columns are below 80%
Excelchat Expert 16/05/2018 - 11:54
ok let me check
User 16/05/2018 - 11:55
=IF(OR(H276>79,I276>79%,J276>79%),"YES","")
User 16/05/2018 - 11:56
That is the exact filter and it is working except it is showing YES for last column that has --
Excelchat Expert 16/05/2018 - 11:56
can you show an example of the exact value? dash like - or two --?
User 16/05/2018 - 11:56
two
User 16/05/2018 - 11:56
--
Excelchat Expert 16/05/2018 - 11:58
ok let me double check the formula for you
User 16/05/2018 - 11:59
ok
Excelchat Expert 17/05/2018 - 12:07
can you show the formula for cells with dashes?
User 17/05/2018 - 12:08
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"--",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert 17/05/2018 - 12:08
Can you remove the dashes inside "--"?
Excelchat Expert 17/05/2018 - 12:09
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert 17/05/2018 - 12:10
just put blank instead of double dashes since excel is reading double dashes as numbers
Excelchat Expert 17/05/2018 - 12:12
Hello did it work>
User 17/05/2018 - 12:12
It is still calculating
Excelchat Expert 17/05/2018 - 12:13
ok, let me know if you're good with the resolution
User 17/05/2018 - 12:17
It is giving me that inconsistent formula error
Excelchat Expert 17/05/2018 - 12:18
did you drag down the formula where you change "--" to ""?
User 17/05/2018 - 12:18
Yes
User 17/05/2018 - 12:18
Even the first cell is giving me that error
Excelchat Expert 17/05/2018 - 12:18
whats the formula you have in column a first row?
User 17/05/2018 - 12:18
It is calculating correctly
User 17/05/2018 - 12:19
The old percentages are still the same
Excelchat Expert 17/05/2018 - 12:19
I need to see the formula to check
User 17/05/2018 - 12:19
=IF(ISNA(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)),"",(VLOOKUP([@[Hub/Path/Circuit]],'Thompson One List - May'!A:F,6,FALSE)))
Excelchat Expert 17/05/2018 - 12:19
ok, how about the 2nd column?
Excelchat Expert 17/05/2018 - 12:19
first row
User 17/05/2018 - 12:20
Everything else is fine
User 17/05/2018 - 12:20
all the data is fine
User 17/05/2018 - 12:20
I am just getting that little green pointer
Excelchat Expert 17/05/2018 - 12:20
except for what?

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.