Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
I have created a LOOKUP Function (as an IF=0 statement) for one column
of information (looking up the value in Column E - my "Item #", =IF(D3=0,"",(LOOKUP(E3,B:B,C:C))) but I want to modify this to also show the look-up for "Vendor" from the value in Column D. I want to have two columns search and two c9olums lookup in order to separate "Vendor" from "Item #". What formula would allow me to do a LOOKUP on both Vendor (COL A) and Item # (COL B) and then return the Item Descritpion Value in COL C? My input culumns are COL D (vendor) and COL E (Item). My result column is COL F. Look UP Table Input Search COL A COL B COL C COL D COL E COL F VENDOR Item # Item Description Vendor Item Description Windham 1812 WND Chaise Lounge Gloster C20 GLO Chaise Lounge (THIS IS WHERE I HAVE LOOKUP) Windham 1813 WND End Table Windham 1814 WND Coffee Table Windham 1815 WND Cuddle Chair Windham 1816 WND Dining Table Windham 1817 WND Dining Arm Chair Windham 1818 WND Dining Side Table Lane 456 LV Loveseat Lane 456 LV Sofa Lane 458 LV Settee Gloster 356J GLO Chaise Lounge Gloster C70 GLO End Table Gloster 359J GLO Coffee Table Gloster C20 GLO Cuddle Chair Gloster 358 GLO Dining Table |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
What formula would allow me to do a LOOKUP on both Vendor (COL A) and
Item # (COL B) and then return the Item Description Value in COL C? Something like this should work fine, array-entered** in F3, then copied down: =IF(OR(D3="",E3=""),"",INDEX(C$3:C$100,MATCH(1,(A$ 3:A$100=D3)*(B$3:B$100=E3),0))) **Press CTRL+SHIFT+ENTER [CSE] to confirm the formula (instead of just pressing ENTER) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote in message ... I have created a LOOKUP Function (as an IF=0 statement) for one column of information (looking up the value in Column E - my "Item #", =IF(D3=0,"",(LOOKUP(E3,B:B,C:C))) but I want to modify this to also show the look-up for "Vendor" from the value in Column D. I want to have two columns search and two c9olums lookup in order to separate "Vendor" from "Item #". What formula would allow me to do a LOOKUP on both Vendor (COL A) and Item # (COL B) and then return the Item Descritpion Value in COL C? My input culumns are COL D (vendor) and COL E (Item). My result column is COL F. Look UP Table Input Search COL A COL B COL C COL D COL E COL F VENDOR Item # Item Description Vendor Item Description Windham 1812 WND Chaise Lounge Gloster C20 GLO Chaise Lounge (THIS IS WHERE I HAVE LOOKUP) Windham 1813 WND End Table Windham 1814 WND Coffee Table Windham 1815 WND Cuddle Chair Windham 1816 WND Dining Table Windham 1817 WND Dining Arm Chair Windham 1818 WND Dining Side Table Lane 456 LV Loveseat Lane 456 LV Sofa Lane 458 LV Settee Gloster 356J GLO Chaise Lounge Gloster C70 GLO End Table Gloster 359J GLO Coffee Table Gloster C20 GLO Cuddle Chair Gloster 358 GLO Dining Table |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
On Mar 1, 6:15*am, insitedge wrote:
I have created a LOOKUP Function (as an IF=0 statement) for one column of information (looking up the value in Column E - my "Item #", =IF(D3=0,"",(LOOKUP(E3,B:B,C:C))) but I want to modify this to also show the look-up for "Vendor" from the value in Column D. I want to have two columns search and two c9olums lookup in order to separate "Vendor" from "Item #". What formula would allow me to do a LOOKUP on both Vendor (COL A) and Item # (COL B) and then return the Item Descritpion Value in COL C? My input culumns are COL D (vendor) and COL E (Item). My result column is COL F. * * * * * * * * * *Look UP Table * * * * * * * * * * * * * * * * * * * * * Input Search COL A * * * * * COL B * * * * *COL C COL D * * * * * *COL E * * * * * COL F VENDOR *Item # *Item Description * * * *Vendor *Item * *Description Windham 1812 * *WND Chaise Lounge * * * Gloster C20 * * GLO Chaise Lounge (THIS IS WHERE I HAVE LOOKUP) Windham 1813 * *WND End Table Windham 1814 * *WND Coffee Table Windham 1815 * *WND Cuddle Chair Windham 1816 * *WND Dining Table Windham 1817 * *WND Dining Arm Chair Windham 1818 * *WND Dining Side Table Lane * *456 * * LV Loveseat Lane * *456 * * LV Sofa Lane * *458 * * LV Settee Gloster 356J * *GLO Chaise Lounge Gloster C70 * * GLO End Table Gloster 359J * *GLO Coffee Table Gloster C20 * * GLO Cuddle Chair Gloster 358 * * GLO Dining Table First, thanks. I could not have conceived that formula. But when I entered it, I receive an #NA. When I remove the values (vendor name and item name) from cells D3 and E3, leaving those cells blank, the #NA disspaeras indicating that if those cells are blank nothing will be displayed, but I don;t understand why I am receiving an #NA. Any thoughts? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
Did you remember to "array-enter" the formula ie to press CTRL+SHIFT+ENTER
[CSE] to confirm the formula (instead of just pressing ENTER)?? If you did the above confirmation correctly, you should see Excel wrap curly braces: { } around the formula in the formula bar. If you don't see it, click inside the formula bar and try the CSE again. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote First, thanks. I could not have conceived that formula. But when I entered it, I receive an #NA. When I remove the values (vendor name and item name) from cells D3 and E3, leaving those cells blank, the #NA disspaeras indicating that if those cells are blank nothing will be displayed, but I don;t understand why I am receiving an #NA. Any thoughts? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
If you still receive #N/A despite correctly array-entering the formula, and
it looks like there should be matches, then it could be that the source data and/or lookup data contains extraneous white spaces which is throwing the matching off. We could try wrapping TRIM in the earlier expression, viz array-entered in F3, copied down: =IF(OR(TRIM(D3)="",TRIM(E3)=""),"",INDEX(C$3:C$100 ,MATCH(1,(TRIM(A$3:A$100)=TRIM(D3))*(TRIM(B$3:B$10 0)=TRIM(E3)),0))) Attached is a sample with the above implemented & wroking fine for easy reference: http://www.freefilehosting.net/download/3d0m7 index n match on 2 col criteria.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
On Mar 2, 12:01*am, "Max" wrote:
If you still receive #N/A despite correctly array-entering the formula, and it looks like there should be matches, then it could be that the source data and/or lookup data contains extraneous white spaces which is throwing the matching off. We could try wrapping TRIM in the earlier expression, viz array-entered in F3, copied down: =IF(OR(TRIM(D3)="",TRIM(E3)=""),"",INDEX(C$3:C$100 ,MATCH(1,(TRIM(A$3:A$100)*=TRIM(D3))*(TRIM(B$3:B$1 00)=TRIM(E3)),0))) Attached is a sample with the above implemented & wroking fine for easy reference:http://www.freefilehosting.net/download/3d0m7 index n match on 2 col criteria.xls -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- * I have the formula working. It was as simple as C-S-E within the formula box. I made some tweaks to properly reference columns and number of rows within a column, but now I have another question. I have moved my Vendor Table of Items (columns: vendor, Item, description and price) to a different worksheet within the same file and successfully referenced that worksheet (allowing me to keep my price list in a separate worksheet for updating, etc. Please help with the following. Once the formula looks up the proper Item, I want to grab the price of that item which is in a column adjacent to that item look-up and place it in my new table on the dsame row but different column. So, for each Item looked up there is a corresponding Price (like $423.00). I suspect this formula is simpler and I still want to use the =IF(OR(A6="",B6="") so the cell is blank unless there's a value. Can you help me with that new formula for the Price column? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
To change the return col, just adjust the INDEX part of the expression:
... INDEX(C$3:C$100, ... ) to say: ... INDEX(Z$3:Z$100, ... ) (assuming the return is to come from col Z which contains the prices) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote I have the formula working. It was as simple as C-S-E within the formula box. I made some tweaks to properly reference columns and number of rows within a column, but now I have another question. I have moved my Vendor Table of Items (columns: vendor, Item, description and price) to a different worksheet within the same file and successfully referenced that worksheet (allowing me to keep my price list in a separate worksheet for updating, etc. Please help with the following. Once the formula looks up the proper Item, I want to grab the price of that item which is in a column adjacent to that item look-up and place it in my new table on the dsame row but different column. So, for each Item looked up there is a corresponding Price (like $423.00). I suspect this formula is simpler and I still want to use the =IF(OR(A6="",B6="") so the cell is blank unless there's a value. Can you help me with that new formula for the Price column? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
On Mar 2, 9:46*am, "Max" wrote:
To change the return col, just adjust the INDEX part of the expression: .. INDEX(C$3:C$100, ... ) to say: .. INDEX(Z$3:Z$100, ... ) (assuming the return is to come from col Z which contains the prices) -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote I have the formula working. *It was as simple as C-S-E within the formula box. *I made some tweaks to properly reference columns and number of rows within a column, but now I have another question. I have moved my Vendor Table of Items (columns: vendor, Item, description and price) to a different worksheet within the same file and successfully referenced that worksheet (allowing me to keep my price list in a separate worksheet for updating, etc. *Please help with the following. *Once the formula looks up the proper Item, I want to grab the price of that item which is in a column adjacent to that item look-up and place it in my new table on the dsame row but different column. *So, for each Item looked up there is a corresponding Price (like $423.00). I suspect this formula is simpler and I still want to use the =IF(OR(A6="",B6="") so the cell is blank unless there's a value. *Can you help me with that new formula for the Price column? Yes, I adjusted the reference columns, and it seems to be working. I appreciate your work, so very much. Jim - Denver, Colorado USA |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LookUp Function with Two Column Search Returning One Column Value
Glad to hear that, Jim. You're welcome.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "insitedge" wrote Yes, I adjusted the reference columns, and it seems to be working. I appreciate your work, so very much. Jim - Denver, Colorado USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup returning value from antoher column in same row (Text!) | Excel Discussion (Misc queries) | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Highest value in column b returning column a | Excel Worksheet Functions | |||
Excel - returning column headers in a seperate column | Excel Discussion (Misc queries) | |||
Lookup function returning reference, not value | Excel Worksheet Functions |