Look up & auto populate between sheets

S

Shek5150

If I have two cells on Sheet 1 (cells A1 & A2) Where A1 is where I’d enter
someone’s IQ score and A2 would represent their percentile rank. Sheet 2 is
a two column table w/ all possible IQ scores (Column A) and Percentile Ranks
(Column B).

My question, is it possible to use a lookup function to look up a percentile
rank (from sheet 2). For example, if I enter an IQ of 130 in cell A1 (sheet
1) and excel would look up the corresponding Percentile Rank (sheet 2) and
then bring this Percentile Rank value forward to sheet 1 and autopopulate
cell A2.

"it seemed much more clear when it was just in my head..."

Thanks in advance.

Steve
 
C

Christi

Is it possible to make the end result cells calculate? I am doing something
similar in a mileage log for staff. Based on the client selected, a number of
miles is returned from a list. Since this is a form template that will be
filled out by staff, I copied the formula to about 10 rows so the fields will
automatically populate. My problem is that I need all of these mileage
totals to sum but am getting #N/A and so my column will not total. Is there
a different way to do this so that I will be able to sum the mileage total
column?

Thank you!!
Christi
 
M

Max

Easier to arrest it at source by using IF(ISNA(...),0, .. error traps
to return zeros for any unmatched cases

Downstream calcs will then not be affected by any #N/As
resulting from unmatched cases

So you could use something like this instead:
In A2:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
 
C

Christi

That's perfect! Thank you!!!!

Max said:
Easier to arrest it at source by using IF(ISNA(...),0, .. error traps
to return zeros for any unmatched cases

Downstream calcs will then not be affected by any #N/As
resulting from unmatched cases

So you could use something like this instead:
In A2:
=IF(ISNA(VLOOKUP(A1,Sheet2!A:B,2,0)),0,VLOOKUP(A1,Sheet2!A:B,2,0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto fill cell based upon another cell value 3
Average values for a given rank 3
Rank based on 2 categories 6
Calculate data from different sheets 4
Setting up macro betwen two sheets 1
If statement with vlookup link to multiple sheets 4
Rank columns based on value in another column 1
Auto-Alphabetize 1

Top