Can VLOOKUP be used in this way?

[AndrewHirst]

Hi,
I'm relatively new to Excel formulas, so bear with me here. My problem is this: I have a main sheet (Sheet1) which has a table (Table1) with several columns - blog URL (self-explanatory), status (whether an article idea has been pitched, sent, or is live), and some other columns that aren't important here. I've got a huge list of sites in another sheet (Sheet2) and I want to see whether they are a) in Sheet1 and b) live, pitched, or sent. So far I have a formula (=IF(ISERROR(MATCH([@Tech],Sheet1!$A$4:$A$465,0)),"FALSE","TRUE")) which tells me whether the sites in column Tech in Sheet2 are in Sheet1. So far, so good. Now I have a situation where I know some urls are in Sheet1, but I don't know if they are live, pitched, or sent without manually checking that column in Sheet1. I've experimented with a VLOOKUP (=VLOOKUP([@Tech],Table1,3,0)) which is only sporadically working (column 3 of Table1 is the column with the "status", i.e. live, pitched, or sent). Most of the time it spits out #N/A. In some cases I see the problem; there are several urls which have either been pitched to several times or have been pitched to and then gone live. There are multiple responses VLOOKUP could return. Thing is, I just want to know if the url is in Sheet1 and if it is live. Is there any way I can do this?

Thanks so much for reading through all of that. I've wasted most of my day trying to solve this! Any help is hugely appreciated.

Andy

 

[mcorriga]

Andy,

Vlookup should return the first result it finds, so multiple matches should not create an #N/A result. There may be a problem with your data or your formula. As far as finding the most updated status goes, the only way I can think to do it with a formula would be if you sort your data in order of Live > Sent > Pitched so that it gets the most updated result. The only other way I can think to do it is with VBA.

 

[Marcelo Branco]

Hi Andy,

Assuming Sheet1 as below and if the Current Status is the last instance, from up to bottom, then maybe this can work

Sheet1

A B C[TABLE="class: grid, width: 180"]
<tbody>[TR]
[TD="width: 74, bgcolor: transparent"]Url
[/TD]
[TD="width: 64, bgcolor: transparent"]Header
[/TD]
[TD="width: 64, bgcolor: transparent"]Status
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url1
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Sent
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url2
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Live
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url1
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Pitched
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url1
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Sent
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url2
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Pitched
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url1
[/TD]
[TD="bgcolor: transparent"]xxxx
[/TD]
[TD="bgcolor: transparent"]Live
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

A B[TABLE="class: grid, width: 180"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]url
[/TD]
[TD="width: 96, bgcolor: transparent"]Current Status
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url1
[/TD]
[TD="bgcolor: transparent"]Live
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url2
[/TD]
[TD="bgcolor: transparent"]Pitched
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]url3
[/TD]
[TD="bgcolor: transparent"]Not present
[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B2 (Excel 2007 or higher)
=IFERROR(LOOKUP(2,1/(TRIM(Sheet1!$A$2:$A$7)=TRIM(A2)),Sheet1!$C$2:$C$7),"Not present")
copy down

M.

 

[AndrewHirst]

Marcelo,
That is amazing - works perfectly. Thank you so much!

Andy