LookUp Function with Two Column Search Returning One Column Value

I

insitedge

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
 
M

Max

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?
Click to expand...

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

---
 
I

insitedge

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
Click to expand...

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?

(e-mail address removed)
 
M

Max

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.

---
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?

(e-mail address removed)
 
M

Max

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$100)=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

---
 
I

insitedge

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$100)=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
---  
Click to expand...

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?
 
M

Max

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)

---
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?
 
I

insitedge

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
---
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?
Click to expand...

Yes, I adjusted the reference columns, and it seems to be working. I
appreciate your work, so very much.

Jim - Denver, Colorado USA
 
Top