Formula over two pages

P

phil2006

My data sheet is too large to fit on one sheet as there are not enoug
columns. Is it possbile to have a formula that reads from both sheets
The formula that has been working for one sheet is:

=((VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))


Any help would be great!!

Thank
 
M

Muhammed Rafeek M

I am not sure what you want, but here i am giving some solution according to
my understanting:
=IF(ISERROR(VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet3!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))

If you are not satisfied, pls do mail to [email protected] with your
workbook.
 
P

phil2006

It's not entirely what I meant. My problem is that I have a distance
chart with more places along the top row than there are columns. Thus
the distance chart must be over two sheets. Whn I type two places into
the original sheet the distance is read using the above formula. I now
need to edit the fromula so that it can read data from both distance
sheets.

Thanks
 
S

SimonCC

If I understood correctly, you can try:
=IF(ISNA(MATCH(D3,sheet2!$A$1:$IK$1,0)),VLOOKUP(B3,sheet3!$A$1:$IK$400,MATCH(D3,sheet3!$A$1:$IK$1,0),FALSE),VLOOKUP(B3,sheet2!$A$1:$IK$400,MATCH(D3,sheet2!$A$1:$IK$1,0),FALSE))

This is assuming that your second data sheet is in Sheet3 and you have the
same number of columns in data as Sheet2. If not, just adjust the reference
accordingly.

Also assuming that value in D3 is always in either of the two datasheets.
If that's not true, you'll need to nest another if to handle situations when
D3 doesn't match anything in the datasheets.

Lastly, make sure the first column in Sheet2 is also in Sheet3 so the
VLOOKUP will work when looking up in Sheet3.

-Simon
 
Top