NFT: Any Excel experts?

Try this...

=VLOOKUP(L2,Sheet1!$A$2:$A$119,12,FALSE)


Also, sort column A smallest to largest.

In comment 14534884 NY17NE14 said:

Quote:

Try this... =VLOOKUP(L2,Sheet1!$A$2:$A$119,12,FALSE) Also, sort column A smallest to largest.

Still showing N/A or REF in each column

1000x better than v lookup

In comment 14534889 ajr2456 said:

Quote:

In comment 14534884 NY17NE14 said: Quote: Try this... =VLOOKUP(L2,Sheet1!$A$2:$A$119,12,FALSE) Also, sort column A smallest to largest. Still showing N/A or REF in each column

Are the 2 columns being compared the same format? For example, if one is formatted as a number and the other text then that can cause problems.

In comment 14534891 V.I.G. said:

Quote:

1000x better than v lookup

This

In comment 14534891 V.I.G. said:

Quote:

1000x better than v lookup

This worked thanks

should be A though L, you have it A through A

but also a couple finicky excel things I've found:

-I've multiple times had it dislike returning values from anything but the 1st column in your lookup range. Can't say why, but moving the column in question to first in line has worked

-Type the formula vs copy / paste. Usually not been an issue when I copy using fill down, but ctrl + v, even with the same exact value (chars & spaces too), errors

-Whether it's cell format, or something else, I've even had to delete the blank column BEFORE entering values, as a procedural step each time on certain spreadsheet files

-Unless I have different tables or more info below the lookup table, I just use the columns labels. A:L vs A1:L109. Made many functions easier, esp for live docs w/ new data rows over time (month to month or whatever). Ref is still accurate, and no fill downs will get F'd up w/ the incorrect row number refs

But yes, +4 to Index Match

In comment 14535043 CMicks3110 said:

Quote:

should be A though L, you have it A through A

Even though they’re in two different sheets?

All the emails are in column L in EmailList and the emails are all in column A in Sheet1

Your formula worked for me when I tested it out. Odd.

In comment 14535090 ajr2456 said:

Quote:

In comment 14535043 CMicks3110 said: Quote: should be A though L, you have it A through A Even though they’re in two different sheets? All the emails are in column L in EmailList and the emails are all in column A in Sheet1

I think your lookup range would have to be more than 1 column. Otherwise it doesn't have a column from which to return a value

Right now, it takes your 1st sheet email address, then looks it up in the 2nd sheet, but has no other column in that row to return a corresponding value

It doesn't have to. I tested it out.

My instinct is the formatting might be different?

sorry to try to explain it better using the example:

You use the email address in L2 to match to another sheet, Sheet1 column A

But in the formula, the "1" means to return the value from the first column in the range. So it's looking up the value in col A, to return the value in col A...but should be returning a value from any other column

In comment 14535105 BrettNYG10 said:

Quote:

It doesn't have to. I tested it out. My instinct is the formatting might be different?

Got it ok, but what value does it return? The same email address?

Then presumably he is building another column to get his TRUE/FALSE.

I like Index/Match much better as well. It's a bit harder to nail down though - VLOOKUP is much more intuitive.

For T/F that makes sense (well if it's working...!!!)

Cool thanks

Try two things -

1. Multiple L2 by 1, i.e. L2*1
2. Use text to columns on column A (just hit finish on the first step) and that will convert out of text format

In comment 14534891 V.I.G. said:

Quote:

1000x better than v lookup

I use both but find index/match to not work better for certain things. I feel like there's this index/match cult that makes it sound like vlookup is shit in comparison but that's not always the case. For starters, you have to make sure that column headers match.

When you reference column names, instead of just anchoring and dragging, you have to change the name of the column because otherwise, it'll just have the name of the first column. Maybe there's a workaround but I don't know it.