Find Name with an ID number, VLOOKUP FUNTION!

skywriter

Well-known Member

Joined

Feb 15, 2014

Messages

1,642

Office Version

1. 2016

Platform

1. Windows

• Oct 16, 2014

• #2

What value are you hoping to return? What do you mean by not working? The value you are looking up in your example doesn't appear to be in the list of customers.

The biggest thing I see is that the customer number you are looking up is in the last column in your data and you reference in your formula column 1 as the column you want the returned data to be from?

 

Last edited: Oct 16, 2014

Upvote 0

B

Beyond_avarice

Board Regular

Joined

Nov 13, 2012

Messages

195

Office Version

1. 2007

Platform

1. Windows

• Oct 16, 2014

• #3

Vlookup must have the lookup value on the far left of your table array and can only then look up items to the right of the matching values.

Try using Index and Match.

<code>=INDEX(column with data you want,MATCH(value you are looking for, column which contains this data,0))</code>

 

Upvote 0

J

Jader04

Board Regular

Joined

May 4, 2009

Messages

53

• Oct 16, 2014

• #4

the value i want is the customer name( residential or commercial)

if you see the screen shot, the value is on the other tab.(commercial)

 

Upvote 0

skywriter

Well-known Member

Joined

Feb 15, 2014

Messages

1,642

Office Version

1. 2016

Platform

1. Windows

• Oct 16, 2014

• #5

I see a picture with customer number CU0002 and the other picture has customer numbers that begin with RU, that's why I asked.
If you want to use VLookup the customer number needs to be in the left column and then you specify which column to the right of this the data you want to return is in. Just as stated by Beyond_avarice above. The suggestion to use Index Match would work if the value you want to return is to the left of the customer number.

 

Upvote 0

J

Jader04

Board Regular

Joined

May 4, 2009

Messages

53

• Oct 16, 2014

• #6

the problem here that i have two sheets and return N/A value with the index and match function

i guess i dont know how to put both sheets in the one formula

 

Upvote 0

J

Jader04

Board Regular

Joined

May 4, 2009

Messages

53

• Oct 16, 2014

• #7

It works with one sheet but when you have two sheet how i tell find that number on page of residential or commercial

 

Upvote 0

skywriter

Well-known Member

Joined

Feb 15, 2014

Messages

1,642

Office Version

1. 2016

Platform

1. Windows

• Oct 16, 2014

• #8

Jader04 said:

the problem here that i have two sheets and return N/A value with the index and match function

i guess i dont know how to put both sheets in the one formula

Click to expand...

Use an if statement, if first formula not(isna), then use first formula otherwise use second formula.

 

Upvote 0

J

Jader04

Board Regular

Joined

May 4, 2009

Messages

53

• Oct 16, 2014

• #9

ok i can do it one sheet but i guess i don't know how to use the if(not function
=IF(NOT(INDEX('Recidential Data'!A4:A555,MATCH(D7,'Recidential Data'!I4:I555,1))),IF(NOT(INDEX('Commercial Data'!A4:A555,MATCH(D7,'Commercial Data'!L4:L555,1)))

return Value

 

Upvote 0

skywriter

Well-known Member

Joined

Feb 15, 2014

Messages

1,642

Office Version

1. 2016

Platform

1. Windows

• Oct 17, 2014

• #10

Try this:
Residential is misspelled, but if that how your sheet is actually spelled then leave it alone, otherwise correct the formula.

Code:

=CHOOSE(IF(ISNUMBER(MATCH(D7,'Recidential Data'!I4:I555,0)),1,IF(ISNUMBER(MATCH(D7,'Commercial Data'!L4:L555,0)),2,3)),INDEX('Recidential Data'!A4:A555,MATCH(D7,'Recidential Data'!I4:I55,0)),INDEX('Commercial Data'!A4:A555,MATCH(D7,'Commercial Data'!L4:L555,0)),"No Match Found")

 

Upvote 0