Hello. I cannot find a solution to this problem. Part of the difficulty I am having is even knowing how to word a proper search string. What I am shooting for is to select a billing code from my dropdown menu and then automatically see the other repetitive values that always correspond to that dropdown item appear in their respective cells on the same row.
The only luck i have had has been relative to numbers–I need these text descriptors to appear.
Thanks for any help…
Please attach a sample file with what you want do. (ask voted to allow upload the file).
One way is using VLOOKUP. It looks through a vertical lookup list to select the corresponding cell.
=VLOOKUP(A1,$F$1:$I$3,2,0)
A1 is the value to lookup (billing code)
$F$1:$I$3 is your lookup list. It can be on a separate sheet (I recommend a separate sheet for better management of your list). Note the $ (Dollar Sign) to make your lookup list not become relative as you copy your formula down and right.
2 is the column to pick your lookup from (in this example, 1 corresponds to col F, 2 to Col G, 3 to Col H, 4 to Col I)
0 is if your lookup list is sorted. If omitted, default is True (sorted ascending). If you haven’t sorted it, specify the zero. The lookup will find the first corresponding value in your lookup list. There are some finer details as to the way the lookup works. With a 0, you will get a N/A error if your item is not found in the lookup list. With a 1, the value returned will be the first value lower than your your value to be looked up. I would recommend a 0.
Edit/Addition: If the lookup table is on a separate sheet.
=VLOOKUP($A1,$Sheet2.$A$1:$D$500,2,0)
Sheet2 would of course be the name of the other sheet. I added a $ in front of the A1 ($A1) so that if the formula is copied across rows, the value to lookup will stay to Column A.
Thank you so much for the response. This is way deeper into spreadsheet voodoo than I am accustomed to going so I have a question right off the bat–if I use a separate sheet, do I name it “$F$1:$I$3”…? I will tinker with this this morning and see what I can figure out. Thanks again!
I am getting closer on this but still working through the syntax and how to deploy it exactly as I need. Here is a link to a screenshot of my spreadsheet. When I input the billing code (RED) I want the other 3 elements (YELLOW) to appear automatically.
EDIT:
finally got it–95%. for some reason (cell Number Formatting issue?), i keep getting #N/A for the Amount column even tho I reference “11” in the formula.
Couldn’t get your screenshot (err 404: object not found). BTW: A functional file is MUCH better than a screenshot.
You may also study an example I made for similar cases. (See answer by “Lupp”)
Hi.
When working with lookup functions I find it easier to think of the last argument as match type. When you set the argument to 0, VLOOKUP requires an exact match.
If there is any difference between the value you are looking up, and it’s corresponding value in the lookup table, a match will not be found.
Things to look out for when VLOOKUP returns an unexpected #N/A error:
Are the ranges in the lookup table correct? This one can still catch me out on a hectic day if I have forgotten to add $ or a range name to the lookup table reference.
Are you comparing apples with apples? Lookup functions don’t recognize the number 1234 as matching the text "1234". When you need to match numeric codes make sure all codes (in both the lookup table and the report) have been converted to numbers or all codes have been converted to text. It doesn’t matter which as long as the data types match.
Watch out for orphaned spaces at the end of a lookup value. These can cause havoc with lookup functions as they prevent matches from being found and are invisible to the naked eye. If you suspect this may be the issue, the TRIM function can be used to remove excess spaces from text.
Hope this helps solve the #N/A woes.
Cheers,
John
Thanks to all who helped me out with this. I finally have a spreadsheet with some fangs…! I can’t believe I waited this long to tweak this document that I’ve been using for the last 2 1/2 years. I would like to upload the final version for posterity in case any other noobs out there stumble on it but I am new to this site and I don’t see the little paper clip icon for uploading files.
Here is a link to find it on dropbox.com: