Vlookup

[bohon79]

Hello,

have in issue I need help with. I have a vlookup in all my other cells that works. The problem I am having is it will not see anything to the left only to the right of the cell. below is the vlookup I have:

=IF(ISERROR(VLOOKUP(B6,PERSONNEL!B2:H500,7,FALSE)),"",IF(LEN(VLOOKUP(B6,PERSONNEL!B2:H500,7,FALSE))=0,"",VLOOKUP(B6,PERSONNEL!B2:H500,7,FALSE)))

it works perfect for all the cells past B2.

the problem is having it look to the left of b2 and returns a N/A.

any help would be thankfull.

Erick

 

[C with no eyes]

VLOOKUP will only look to the right unless you use OFFSET but...

I find INDEX and MATCH are better.

 

[bohon79]

so use offset instead of vlookup

 

[AhoyNC]

You'd be better off using INDEX / MATCH. See attached link.
http://www.excel-easy.com/examples/left-lookup.html

 

[bohon79]

Still confused as it returns a 0

the way I have it setup is as follows

sheet 1 where the data goes into

column a column b
status name

sheet 2 is the main data

column a column b
status name

I want status data on sheet 2 to go into status on sheet 1

Erick

 

[bohon79]

Nevermind, figured it out. thanks for the help

 

[bohon79]

hello,

have one more issue. I found the correct code but now when I have nothing in the cell
I get a N/A. I would like that cell to be blank if nothing is in the cell that it looks at. below is the code that works:

=INDEX(PERSONNEL!$A$2:$A$20,MATCH(HQS!B6,PERSONNEL!$C$2:$C$20,0))

Erick

 

[FormR]

Hi, like this maybe:

=IF(HQS!B6="","",INDEX(PERSONNEL!$A$2:$A$20,MATCH(HQS!B6,PERSONNEL!$C$2:$C$20,0)))

Or if this formula is being placed in the HQS sheet then..

=IF(B6="","",INDEX(PERSONNEL!$A$2:$A$20,MATCH(B6,PERSONNEL!$C$2:$C$20,0)))

 

[bohon79]

thanks