Tring to import data from another sheet providing condition is met

**lancaster** · 04-08-2009, 02:44 PM

Hi I have a workbook which has an overview sheet of user access and subsequent sheets with levels of access.

The one sheet contains Surname, First Name, User ID and Limit in each column.

The other sheet contains User ID and Limits which can be in any random order.

I want the limit column on the first sheet to look at the UserID in that particular row and search to see if the user ID exists in the other sheet. This can be anwhere in the first column. If the User ID matches, then it pulls in the Limit value into the cell where the formula is.

Hope that makes sense!

I have an example workbook attached.

Can you think of a formula which can do this?

Thank! :o)

**JBeaucaire** · 04-08-2009, 07:41 PM

Three answers for you, each one more complete. First, your basic question on how to match the ID to a known limit. In D2 put this formula and copy down to D5:

=INDEX(Limits!$B:$B,MATCH($C2,Limits!$A:$A,0))

Notice the two errors? The cell where the ID was not found and the cell where the data was not there yet both result in an error. To fix that, use this formula in D2 down to D5:

=IF(ISNUMBER(MATCH($C2,Limits!$A:$A,0)),INDEX(Limits!$B:$B,MATCH($C2,Limits!$A:$A,0)),"none listed")

This lists "none listed" on the IDs it does not match...but it also lists "none listed" on D5 where there is no ID at all yet, too. If that's OK, then you're done. If not, one last version to hide the answer altogether until an ID is listed:

=IF(C2="","",IF(ISNUMBER(MATCH($C2,Limits!$A:$A,0)),INDEX(Limits!$B:$B,MATCH($C2,Limits!$A:$A,0)),"none listed"))

Tring to import data from another sheet providing condition is met

LinkBack

Thread Tools

Rate This Thread

Display

Tring to import data from another sheet providing condition is met

Re: Tring to import data from another sheet providing condition is met

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions