more vlookup help please

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
Ok, I have some code written that uses vlookup. Now, I am having a type conversion problem. To take care of text to numbers, I have:

=VLOOKUP(--A1, data, 2, false)

But I also have text in my "data" and using this VLOOKUP gives the error:

#VALUE!

Now, if I switch back to

=VLOOKUP(A1, ...

I get the #NA error in the cells that need to lookup a number and the correct values in the cells that lookup text.

I have numbers and text sprawled throughout the column A in the workbook. So, I can't run one down so many rows and then run the other.

Any help would be greatly appreciated
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hello,

Without attempting to recreate your problem, could you not do an IF(isna

if(isna(lookup method 2,lookup method 2, lookup method 1)

Does this solve your problem?
 
Upvote 0
Have you tried the Text to Columns function of the data menu?

This will allow you to convert all the data in a column to a specific format(ie. Text or general).
Post back if you not sure how to use this option. Make sure you change the search values as well as the first column on the lookup table to the same format. If they are numbers and text, convert them both to text values.

PS- Once you use this function to change cells to text values, formulas will no longer work in these converted cells.
 
Upvote 0
Ok, CBrine, the columns in both spreadsheets are formatted to be text. So, that has been covered.

OnlyADrafter, can you elaborate a little more. I was thinking about an IF statement, but just didn't know how to handle it. I think it would be more efficient to run the if statement on the #VALUE! error versus the #NA error, if that is possible. Do you mind writing an exact example of this?

Thanks
 
Upvote 0
=If(ISna(Vlookup(??,??,??,False)),"",vlookup(??,??,??,false))
Will eliminate the #N/A errors from your spreadsheet for failed matches in the vlookup.

PS-This formula evaluates 2 vlookups for each cell, and is not very effiecient processing wise, but I have used it will table's with up to 15,000 entries with only minimal time delay's.

Hope this helps.
 
Upvote 0
ok, call me an idiot, but this is what i tried

Range("F2").Select
ActiveCell.Formula = "=VLOOKUP(B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE)"

Range("F2").Select
ActiveCell.Formula = "If(ISna(Vlookup(--B2, [DATA.xls]Sheet1!$A$1:$D$63, 2, FALSE)))"


However, it isn't quite right since I am printing "If(ISna..." to the cell instead of the correct info. I know it must be a minor detail, but hmm, any ideas?
 
Upvote 0
PS-This formula evaluates 2 vlookups for each cell, and is not very effiecient processing wise

You can use this and be effiecent by using the setv() and getv() functions (which are included in the morefunc addin) as demonstrated below. This will eliminate the redundandt dual lookup.

=If(ISna(setv(Vlookup(??,??,??,False))),"",getv())

As seen posted by Aladin and some others as well.
 
Upvote 0
Ok Jon, for future reference that is what I will do, I just figured it wouldn't be seen there.

Yes to your question. We now have both text and numeric data as lookup values.

It doesnt produce #NA until a lookup is performed, so I don't understand how to use the IF statement everyone keeps suggesting to me.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top