Need help with If Function between sheets

[pearl11]

I am trying to simplify having to input fixed information.

I have information on two excel sheets/tabs

One sheet is where i want to input data

the other sheet is where i want it to grab the info.

So if I put in 3 in A1, it will look on the second sheet and grab a number from B3 and place this number in A2 (on the first sheet).

If I put in 2 in A1, it will look on the second sheet and grab a number from B2 and place this number in A2 (on the first sheet).

I was able to figure out how to it for one number, but if you ask other numbers it doesn't work.

=IF(A1=3,Sheet2!B3)

so that formula works for the number 3. what if I put in the number 2 or 9 or 211?

Any suggestions?

 

[pearl11]

sorry texanann, using the word counts is confusing

does what i just said before this help explain?

 

[pearl11]

What is your overall goal with this formula?
to save time and cut down on human error from getting data from different sheets

Here is what Sheet 2 looks like:

A B
Town
Weight Cost
1 7.89
2 8.15
3 8.97

 

[MrKowz]

I think I see what you're needing now...

=VLOOKUP(A1,Sheet2!A:B,2,0)

 

[pearl11]

works, that's exactly what i needed

thanks so much

 

[texasalynn]

try it like this

=IF(A4=3,Sheet2!B3,IF(A4=2,INDIRECT("Sheet2!B" & (A1)+1),IF(A1=9,Sheet2!B9)))

 

[pearl11]

thanks tex

I like it when there is more than one way to solve an issue

i don't know if you'd want the additional challenge but what if instead of the same scenario i asked about between two sheets, it is between two excel files like excel1.xlsx and excel2.xlsx

you two really know your stuff.

 

[MrKowz]

When referring between workbooks, you have to refer the workbook AND the worksheet. You have to encase the workbook reference in [square brackets] and encase the workbook AND worksheet reference in 'single quotes'

=VLOOKUP(A1,'[excel2.xlsx]Sheet2'!A:B,2,0)

 

[pearl11]

why am i not surprised you figured that out so quickly.

lol

thanks

 

[pearl11]

=VLOOKUP(A1,Sheet2!A:B,2,0)

this formula works incredibly well

It is late afternoon and I haven't had a coffee today so what usually comes easy isn't today.

I am trying to figure out what the A:B, 2,0 part of the above formula means.

any quick explanation?

 

[MrKowz]

A:B refers to columns A through B.

2 refers to the column in the array to return (A is the first column, B is the second column)

0 refers to a TRUE/FALSE boolean, where TRUE will return the closest match (data must be sorted), and FALSE will return only an exact match.