VLOOKUP is returning the "title" of my column in the correct cells.

[cluelessatwork]

Hi! New to all of this, and even Excel programming. I recently learned how to use the VLOOKUP formula and love it. I mainly use it at work to help with billing. However, I'm getting stuck, and need help. Basically, all of my VLOOKUP formulas work, except for this one. I have my "Description" and "Prices" in columns A and B. The VLOOKUP formula seems to be finding the Descriptions fine, but instead of returning a price, it will literally return the word "Price" as a value. Maybe I'm not describing the issue thoroughly, again, I'm new to this. Can somebody please help me and let me know what I'm doing wrong?

Also, on one tab, I have my worksheet with the VLOOKUP formula. Where I want it to start, then the table array which is on the second tab, I enter 2 for the column, and true, since I want a sort of match. On tab two, where I have the "Description" and "Prices", there is a formula to come up with the price, something like =(C2*D2+7.25) Could that be the problem? Also, there is text and numbers in tab 1 vs tab 2, only has numbers. I want VLOOKUP to find the NDC numbers, and give me the price (derived from the formula).

:

[NBVC]

It is not very clear what you are looking up.

Can you specifically make references to sheetnames, what item is being looked up and where it should be found, then what should be returned.

Are you sure you posted the right sample workbook? Doesn't seem to match your description above?

[cluelessatwork]

Sorry about that.. CLUELESS. Ok. So the worksheet has two tabs. One is BILL, the other is CPT. I want the VLOOKUP to find the NDC located in column C of the Bill tab and return the price to the omfs column. The price is located in the CPT tab, column A. So basically, match the NDC value that is mixed with letters in BILL, to NDC value in CPT tab. After the match is made, I want it to return the price in CPT tab, to OMFS in Bill tab. Make sense?

[NBVC]

So, assuming your NDC are the last 11 digits of the column B entries, try:

=IF(ISERROR(INDEX(CPT!A:A,MATCH(--RIGHT(C2,11),CPT!B:B,0))),"",INDEX(CPT!A:A,MATCH(--RIGHT(C2,11),CPT!B:B,0)))

copied down

A blank is returned if there is no number found in column B or a match is not found in the other sheet.

[cluelessatwork]

YES, something like that!!!!! Amazing, genius! Ok. so since I'm new at this.. Where exactly am I pasting this formula?

[NBVC]

YES, something like that!!!!! Amazing, genius! Ok. so since I'm new at this.. Where exactly am I pasting this formula?

The formula goes in D2 of the Bill sheet, then copied down.

[cluelessatwork]

The formula goes in D2 of the Bill sheet, then copied down.

this is great, thank you so much! i was still wondering if you would be available as a paid consultant for our company? i'm not sure if this is appropriate or not, i didn't really read the rules of the forum, i needed some desperate help.

i still have some questions i would like to understand the formula, so that i can apply it to other worksheets, etc.

[GeneralDisarray]

you might try the following (also, not sure from the book you sent which price column to use...in my example sheet i used column D from the CPT tab). You would need to fill that column with prices for the formula to work.

Basically, with the NDC not always having the same length you have a bit of a pickle.

1) copy C column (bill tab) and paste it into another column (i used F).

2) then highlight just the filled cells (which was F3 to F95) and press cntrl+f

3)
search for *NDC and replace all (with a blank replace box).
search for *# and replace all (with a blank replace box)

That creates a column of NDC numbers w/o the extra text -- Ideally, you need either a consistent length for the NDc or another column containing the NDC numbers (on the bill tab) without the other text.


Ok, then you need to reauthor the VLOOKUP to match off the F column (see attached).
Does that help?

[cluelessatwork]

I pasted the formula after the VLOOKUP formula, and it gives me a false value for the matching NDC numbers. The NDC numbers are being picked up fine.. just not the prices from the tab called CPT under column A. I need for that price to return to tab 1 called BILL, under the colum omfs.

[cluelessatwork]

I was able to download the spreadsheet! VERY close to what I want Just need the prices that show up on the OMFS to be column A only. By the way, do you offer any type of Excel courses? Even if they're paid? Would love to change the way things are done around here, and you are very knowledgeable!

[GeneralDisarray]

sweet! (i hope you meant my sheet )

One note about VLOOKUP you might find helpful. I noticed you set the last Parameter to "true" in your sheet (which i changed to "false"). When looking up an ID number or code (like your NDC dealo) you should probably always use false...that ensures an exact match -- selecting "true" will return an approximate match (and requires the sheet to be sorted).

"true" is sometimes useful.(like a grade book where you might match a 85% grade to a B because it's below 90%...or something similar) but when looking for an exact match only, always use false.

_________________________________

If you like my answer - please increase my reputation in the upper right-hand side of this post

[cluelessatwork]

i rated this page as excellent! anyway, still waiting on your response regarding the total+service fee column. I realized I kept calling it "price". That is the column i need to come back i'm not sure what this MAX thing is.. that is a table of the prices of the medications and how they have changed in the past. the entry person will type in the price they want on column d or year price and that should give me the answer for column a. i hope i'm not confusing you more. if you help me with this, i will be soooooooooooooo greatful, almost there!!!

[GeneralDisarray]

sorry, got your PM ... thought you were good to go.

A word to the wise - you may want to get familiar with Index / Match (the function the moderator was using). It's a bit better than VLOOKUP in one way (that i know of).

With Vlookup your "lookup" column (in this case the NDC number column) must be on the left hand side of the table (MUST!).

Therefore, i moved your column from B to A so you could use VLOOKUP.

This sheet pulls the information from the Price column, so if someone filled out column D on that sheet...they would populate the price column and the information will pull to the other sheet's vlookup column...

[NBVC]

...
A word to the wise - you may want to get familiar with Index / Match (the function the moderator was using)...

I prefer to be referred to as NBVC.

[GeneralDisarray]

i hate to say that i'm so new to the forum....that i'd already gone "advanced" to attach my sheet and didn't realize (as i now do)...that i could still see the previous postings by scrolling down...

thank you NBVC!