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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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?
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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.
 

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
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
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
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?
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473

ADVERTISEMENT

FYI, original thread . I think you'd be better off posting your follow-up there. :)
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
So, now we've got both numeric and text formats in the common search column?
 

Cam

Board Regular
Joined
May 29, 2002
Messages
167
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.
 

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,657
Messages
5,597,391
Members
414,142
Latest member
Banyangt

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
Top