• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Look up table not working

Hi all,

I asked a similar question before here regarding lookup tables. But I guess I didn't completely "get it".

Because I'm doing another excel sheet referencing a lookup table and it's not working.

Attached is my botched excel file snippet, with sample data.

Any suggestions on how to fix this.  I promise this time I will finally "get" the lookup table concept.  :)

BTW this is the formula I am trying to adapt

=IFERROR(VLOOKUP(B2,Table1,FALSE),"Not Found")

Open in new window


What I am trying to do is pull in the First Name {Column1)based on Property Street Number and Name (Column2)

Thanks!

Rowby
redoing-lookup-1-file.xlsx
0
Rowby Goren
Asked:
Rowby Goren
3 Solutions
 
nutschCommented:
You're missing the column number in your vlookup (should be the third parameter). In addition, for your vlookup to work, you need to match the first column of a range and return something to the right of that.

With the setup you have, you need either to move address to the left of name, or use an index / match structure, as in:
=INDEX('Lookup table'!$A$2:$A$5,MATCH(B3,'Lookup table'!$B$2:$B$5,0))

Thomas
0
 
als315Commented:
Will be good if you show expected result.
For B2 cell you should use this:
=IFERROR(VLOOKUP(A2,Table1,2,FALSE),"Not Found")
0
 
Rowby GorenAuthor Commented:
Hi als315,

Thanks for helping.

Here's a file with the expected results.  Let me know if your formula will work with the attached file.

Rowby
redoing-expected-results.xlsx
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
NBVCCommented:
als315's formula is correct for the attached file.

you can also add check for blank in column A, so that no result returned if there is no lookup value...

=IF(A2="","",IFERROR(VLOOKUP(A2,Table1,2,FALSE),"Not Found"))
0
 
Rowby GorenAuthor Commented:
Now I am using the formula on the actual spreadsheet.

So I have to slightly modify the formula because the column on the main page (not the Table1) page is different.

What does the =IF(A2= refer to.   The look up table column or the main page column.

Also what does the second A2 refer to -- the main page A2 or the look up table column A2?)

Thanks!

Rowby
0
 
NBVCCommented:
The A2 in both instances it the cell that contains the item you want to lookup in the table.

A2 is in the main page (where you are putting the formula).
0
 
Rowby GorenAuthor Commented:
Sorry for my confusion.  But doesn't the lookup table need the street address in order to link the first name to the last name.

Here's yet another version of my excel file, Missing the formula -- because I think I have my version of the formula all screwed up.  This version matches my actual main page of my excel file -- with dummy data put in.
exerts-exchange-confused.xlsx
0
 
NBVCCommented:
I am confused as to what you are trying to accomplish now.

Exactly which cells are to contain the formula you are looking for?

What exactly is being looked up and returned?
0
 
Rowby GorenAuthor Commented:
I have a spreadsheet that when it was created the first name was not included. Only the last name.  So I am trying to fix the spreadsheet by adding the missing first name.

I want the first name looked up based on the street address

I want Column "i" which is headed "Missing First Name" to get that first name based on the street address (which is Column K)

So I assume the lookup table needs to include a column with a list of street addresses and the corresponding first name.

:)

Thanks

Rowby
0
 
NBVCCommented:
The street address is not in the lookup table, so how will you lookup the last name and street address in the look up table?
0
 
Rowby GorenAuthor Commented:
Ah, you're right.

HEre's a modified version of the file.  With street address in the lookup table.
exerts-exchange-confused-added-a.xlsx
0
 
NBVCCommented:
Ok, we'll need an alternative to VLOOKUP since the lookup column is to the right of the return items column....

try INDEX/MATCH

e.g.

=INDEX(Table1[[#All],[First Name]],MATCH(K2,Table1[[#All],[Street address]],0))

copied down.

or

=INDEX(A:A,MATCH(K2,B:B,0))
0
 
Rowby GorenAuthor Commented:
Hi  NB_VC

I tried adding both formulas to the file, but no result.  Please look at the attached.  I included one of your formulas.

I'm sure we are "getting there"! :)

Thanks

Rowby
exerts-exchange-added-address-to.xlsx
0
 
NBVCCommented:
You entered the formula in the cell twice!

See attached.
Copy-of-exerts-exchange-added-ad.xlsx
0
 
Rowby GorenAuthor Commented:
Great, NB_VC!

Working.  Now I'm going to apply it to my actual file and award the points (will be accepting multiple solution.)

ROwby
0
 
Rowby GorenAuthor Commented:
Thanks NB_VC and  nutsch and als315!

NB_VC example spreadsheet did the trick!

Rowby
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now