2 woorksheets 1 workbook, help with lookup

[David D]

1st worksheet;
fill in form cell A1= inches_____
cell A2=gallons_____

2nd worksheet=lookup sheet

Column A= inches
Column B = gallons

the inches column goes from 1 - 98 down 2 pages with headings on both pages,
I need 1st worksheet to lookup the inches entered and fill in the gallons
from the 2nd worksheet.

this happens in 6 locations on the 1st. worksheet, I can modify the initial
solution for the other locations, just need help getting the 1st. lookup
going.

Thanks in advance, David

 

[Sandy Mann]

Try:

=IF(B1="","",VLOOKUP(B1,Sheet2!A1:B98,2,0))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David D]

I'm not sure this works, or am not understanding the lookup...a little help,
thanks

David

 

[Sandy Mann]

Perhaps it is me who is not understanding your requirements.

Here is what I did:

Sheet1:
Cell A1 entered "Inches" (without the quotes)
Cell A2 entered "Gallons" (again without the quotes)

Sheet2:
A1 "Inches"
in A2:A99 the numbers 1 to 98
B1 "Gallons"
in B2:B99 the number of gallons. (in my case for ease of testing I just
entered 10* the number of inches)

Sheet1:
Cell A2 thre formula:

=IF(B1="","",VLOOKUP(B1,Sheet2!A2:B99,2,0))

The first part checks to see of there is anything in cell B1 and if not
returns an empty string which makes the cell look blank.
If there is a number in B1 then the VLOOKUP() searches down the first column
of the range A2:B99 looking for an exact match and, if it finds one it
returns the value from the 2nd column - in this case the same row in Column
B. The zero, (or sometimes you will see people using FALSE), at the end
tells the fuction to look for an exat match.

If it fails to find a match then it returns #N/A. If you don't want to see
the #N/A then use the formula:

=IF(B1="","",IF(ISERROR(VLOOKUP(B1,Sheet2!A2:B99,2,0)),"Error",VLOOKUP(B1,Sheet2!A2:B99,2,0)))

Post back if you still need help.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk