Big Blue Interactive The Corner Forum  
Back to the Corner

Archived Thread

NFT: Any Excel experts?

ajr2456 : 8/18/2019 12:28 pm
Trying to run a V Lookup comparing Column L in the first sheet to column A in Sheet1 and inputting a true false. Using the formula below but it's not working. Both columns contain email addresses.

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


Appreciate any help.
.  
NY17NE14 : 8/18/2019 12:31 pm : link
Try this...

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


Also, sort column A smallest to largest.
RE: .  
ajr2456 : 8/18/2019 12:36 pm : link
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
Get in the Index Match game  
V.I.G. : 8/18/2019 12:37 pm : link
1000x better than v lookup
RE: RE: .  
Steve in ATL : 8/18/2019 12:47 pm : link
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.
RE: Get in the Index Match game  
robbieballs2003 : 8/18/2019 12:50 pm : link
In comment 14534891 V.I.G. said:
Quote:
1000x better than v lookup


This
RE: Get in the Index Match game  
ajr2456 : 8/18/2019 1:15 pm : link
In comment 14534891 V.I.G. said:
Quote:
1000x better than v lookup


This worked thanks
the range  
CMicks3110 : 8/18/2019 2:45 pm : link
should be A though L, you have it A through A
Ya think CMicks nailed it...  
ChaChing : 8/18/2019 3:17 pm : link
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
RE: the range  
ajr2456 : 8/18/2019 3:32 pm : link
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
....  
BrettNYG10 : 8/18/2019 3:35 pm : link
Your formula worked for me when I tested it out. Odd.
RE: RE: the range  
ChaChing : 8/18/2019 3:44 pm : link
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
ChaChing,  
BrettNYG10 : 8/18/2019 3:47 pm : link
It doesn't have to. I tested it out.

My instinct is the formatting might be different?
=VLOOKUP(L2,Sheet1!$A$2:$A$119,1,FALSE)  
ChaChing : 8/18/2019 3:50 pm : link
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
RE: ChaChing,  
ChaChing : 8/18/2019 3:52 pm : link
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?
Yes, the same email.  
BrettNYG10 : 8/18/2019 3:58 pm : link
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.
Yeah, it might be I'm a little off on the exact purpose  
ChaChing : 8/18/2019 4:00 pm : link
For T/F that makes sense (well if it's working...!!!)

Cool thanks
Agreed that it sounds like a formatting issue  
jgambrosio : 8/18/2019 4:06 pm : link
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
RE: Get in the Index Match game  
BestFeature : 8/18/2019 6:16 pm : link
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.
Also when using data tables  
BestFeature : 8/18/2019 6:21 pm : link
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.
Back to the Corner