Searching for a value in an Array

G

Guest

It gave me an error which is #NUM though I checked it many times. The point
is that in the countries list, the country is repeated many times, and in the
machine model column, the machine is repeated many times, you need to create
a lookup function which will search for the machine and then when it founds
the machine search in another column for the country and then when both the
machine and the country matches your search criteria you need to get the
price from another column, which means basically that you have a two columns
lookup procedure.

Regards,
 
B

Bob Phillips

I completely understand how your data is laid out and the formula I gave you
should do what you want.

Show me the exact formula that you used. an example of the data would help
too.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Phillips

I completely understand how your data is laid out and the formula I gave you
should do what you want.

Show me the exact formula that you used. an example of the data would help
too.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

I attached below the data, I am sorry it is not tidy enough but the first
column is the machine model, then you can see the country and then the price.

D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR
D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR
D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD
D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD
D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD
D51238K-QS USE XJ Version 13459 SYRIA 117 USD
D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD
D51238K-QS USE XJ Version 13461 LIBYA 125 USD
D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR
D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD
D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD
 
G

Guest

I attached below the data, I am sorry it is not tidy enough but the first
column is the machine model, then you can see the country and then the price.

D28886-QS Dewalt Die Grinder. 710W variable speed 13456 IRAQ 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13457 JORDAN 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13458 LEBANON 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13459 SYRIA 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13460 EGYPT 137 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13461 LIBYA 126 USD
D28886-QS Dewalt Die Grinder. 710W variable speed 13462 MOROCCO 116 EUR
D28886-QS Dewalt Die Grinder. 710W variable speed 13463 TUNISIA 134 EUR
D28886-QS Dewalt Die Grinder. 710W variable speed 13464 ALGERIA 140.36 USD
D51238K-QS USE XJ Version 13457 JORDAN 122.9 USD
D51238K-QS USE XJ Version 13458 LEBANON 122.9 USD
D51238K-QS USE XJ Version 13459 SYRIA 117 USD
D51238K-QS USE XJ Version 13460 EGYPT 122.9 USD
D51238K-QS USE XJ Version 13461 LIBYA 125 USD
D51238K-QS USE XJ Version 13462 MOROCCO 110 EUR
D51238K-QS USE XJ Version 13463 TUNISIA 120 EUR
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13456 IRAQ 132.3 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13457 JORDAN 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13458 LEBANON 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13459 SYRIA 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13460 EGYPT 129 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13461 LIBYA 131.3 USD
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13462 MOROCCO 115.5 EUR
D51238K-XJ 18 GA 50mm BRAD NAILER KIT 13464 ALGERIA 139.76 USD
D51256K-QS USE XJ VERSION 13456 IRAQ 183.8 USD
 
B

Bob Phillips

This works fine for me

=INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A100="D51238K-QS USE XJ
Version 13458"),0))

although I do notice that all the machines are unique

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Phillips

This works fine for me

=INDEX(C1:C100,MATCH(1,(B1:B100="LEBANON")*(A1:A100="D51238K-QS USE XJ
Version 13458"),0))

although I do notice that all the machines are unique

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Thanks Bob, it is working great, but why do I need to press CTRL+SHIFT+ENTER
to evalluate this function

Regards,
Farah
 
B

Bob Phillips

Because the double condition test needs to evaluate to an array, and the way
that you tell hat to Excel is bey entering with Ctrl-Shift-Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

Sorry to jump in here but I have a question that runs along the lines of the
original post of this thread but also has a different twist.

In each cell of Page 1, A1:100 I want to search Page 2, A1:50 for a specific
code. If that code is there then I want the amount in Column B of that same
row on Page 2 to be returned. I presume the first part is easily handled by
the first part of the INDEX formula below but how do I get it to return the
correct value?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sliding scale for Mark Up 0
if cell equals "a" then another cell would calculate percentage 1
Help! Is this possible 2
VLOOKUP and IF together 3
Weighted average with changing weights… 1
Max Price and Min Price paid for an item 1
LOOKUP problem 8
Excel Lookup 1

Top