more vlookup help please

[sasoderl]

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

 

[just_jon]

OK; Let me see if I've got this right; the lookup array is text and numeric mix. You have 2 macros, one to do a numeric lookup and 1 to do a text lookup; you want a single VLOOKUP formula applicable to both.

That about it?

 

[just_jon]

Perhaps --

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

 

[sasoderl]

actually, yes. I don't have 2 separate macros, i just keep playing around with the one. You see, if I run them separately, one macro fills in correct data for say numeric values and then running the macro again overwrites all the correct numeric value data when it fills in the correct text value data. So, essentially, I am trying to run one macro that fills in correct values for both at the same time.


What does the "N" stand for in your If statement?

 

[sasoderl]

Using

=VLOOKUP(IF(N(B2),--B2,B2), data, 2, FALSE)

gives me the same results as

=VLOOKUP(B2, data, 2, FALSE)

Which means I am getting correct text lookup values but not getting numeric lookup values.

 

[just_jon]

What happens if the search columns are formatted not as text but as general?

 

[sasoderl]

When I formatted both columns to general, I still get the same results using =VLOOKUP(IF(N(B2), --B2, B2),...

 

[just_jon]

Want to re-send the workbook?

 

[sasoderl]

Will do

 

[just_jon]

Any objection to using some extra columns?

 

[sasoderl]

no objection whatsoever