Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
I have a tab called Status with one column with unique numbers in column B.
On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
See this short video:
http://contextures.com/xlVideos08.html#VLookup01 -- Biff Microsoft Excel MVP "KC" wrote in message ... I have a tab called Status with one column with unique numbers in column B. On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
in B12, you would type your 5512....
Then in C12 =VLOOKUP(B12,Status!$B$2:$H$1000,2,FALSE) expand your table ($B$2:$H$1000) as necessary, and change the column reference (2) as necessary. Currently, this would pull the data in your Status tab from cell C12 (column 2 of the table $b$2:$h$1000) -- John C "KC" wrote: I have a tab called Status with one column with unique numbers in column B. On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
On Sheet5, starting in B1 I have
1 cat apple 2 dog pear 3 horse plum 4 donkey apricot On Sheet4 in A1 I entered 3 In B1 I have =VLOOKUP($A$1,Sheet5!$B$1:$D$4,COLUMN()-1,0) This is copied to D1 Row 1 now shows 3 3 horse plum Any use? Please: your workbook contains WORKSHEETS, while TABS are the things you click to open a worksheet -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "KC" wrote in message ... I have a tab called Status with one column with unique numbers in column B. On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
John, thanks for the help. The formula you gave me below works. One more
question though, if some of the fields are empty, the formula gives me a result of #N/A....is there something that I can add to the formula so it will not display anything if the cell is blank? "John C" wrote: in B12, you would type your 5512.... Then in C12 =VLOOKUP(B12,Status!$B$2:$H$1000,2,FALSE) expand your table ($B$2:$H$1000) as necessary, and change the column reference (2) as necessary. Currently, this would pull the data in your Status tab from cell C12 (column 2 of the table $b$2:$h$1000) -- John C "KC" wrote: I have a tab called Status with one column with unique numbers in column B. On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup Formula - I think?
If the cell is empty on Status, it should display 0, not #N/A. The example I
gave only covers 7 columns (B through H), so if you have more than 7 columns of data, be sure to widen the base. If you are copying the formula across, you may want to first 'anchor' the lookup value, so your formula would start off like =VLOOKUP($B12, ..... this way the column B remains the same (as it is the constant that you are basing all of your lookups on). -- John C "KC" wrote: John, thanks for the help. The formula you gave me below works. One more question though, if some of the fields are empty, the formula gives me a result of #N/A....is there something that I can add to the formula so it will not display anything if the cell is blank? "John C" wrote: in B12, you would type your 5512.... Then in C12 =VLOOKUP(B12,Status!$B$2:$H$1000,2,FALSE) expand your table ($B$2:$H$1000) as necessary, and change the column reference (2) as necessary. Currently, this would pull the data in your Status tab from cell C12 (column 2 of the table $b$2:$h$1000) -- John C "KC" wrote: I have a tab called Status with one column with unique numbers in column B. On my 2nd tab called Triple, the column headings are all the same as on the Status tab. What I'd like to do, is on the Triple tab, if I type in one of the unique numbers from the Status tab, I'd like all the information on that row, populated on the Triple Tab. So, for instance, if Status tab B12 says 5512 and on the Triple tab in cell B2 I type 5512, I want all the information that is already populated on the Status tab row 12, to be populated in the Triple tab row 2. I thought maybe a lookup formula might work, but its not. So maybe I'm going about this all wrong. Can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula - please help! | Excel Discussion (Misc queries) | |||
LOOKUP FORMULA | Excel Discussion (Misc queries) | |||
Lookup formula help | Excel Worksheet Functions | |||
Lookup formula? | Excel Discussion (Misc queries) | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions |