• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hi, Retrieve info with two variables

Daniel Valle

New Member
Database
SKUs Items Prices R

A B C D
A101 BLUE 200
A101 RED 200 R
A101 GREEN 200
B19 BLUE 300
B19 RED 300 R
B19 GREEN 300
D100 BLUE 50 R
D100 RED 50
F405 BLUE 5
F405 RED 5 R
F405 GREEN 5
G23 BLUE 30
G23 RED 30 R
G23 GREEN 30
Question:
What formula is needed to get
the item with this two variables

G23 and "R"
Result: RED
 
=INDEX(B2:B15,MATCH(1,(A2:A15="G23")*(D2:D15="R"),0))

Array FOrmula (press CTRL-SHIFT-ENTER button together)

Or
=INDEX(B2:B15,MATCH(1,IF(A2:A15="G23",IF(D2:D15="R",1)),0))

=INDEX(A2:D15,MATCH("G23"&"R",A2:A15&D2:D15,0),2)
 
Last edited by a moderator:
Hi azumi:
Thank you very much for respond, but everything make sense and I get #VALUE! :(
I don't why...
I review the formulas and are in the correct place but ...I don't know what I did wrong
 
I Try all options, and something is wrong


#VALUE!
=INDEX(B3:B16,MATCH(1,(A3:A16="G23")*(D3:D16="R"),0))

#VALUE!
=INDEX(B3:B16,MATCH(1,IF(A3:A16="G23",IF(D3:D16="R",1)),0))

#VALUE!
=INDEX(A3:D16,MATCH("G23"&"R",A3:A16&D3:D16,0),2)

#VALUE!
=INDEX(B3:B16;MATCH(1;((A3:A16&D3:D16="G23R")*1);0))
upload_2014-7-11_10-29-21.png

upload_2014-7-11_10-31-57.png
:(
 
Somendra.... you deserve a big hug!!!!!

In very simple things, we can make a big mistakes!!! I have no doubt

Thank you very much!!
 
Just for sharing purpose.. :)

To avoid Ctrl + Shift + Enter..
the formula part which is rturning array output.. block that part with Index function.. It work great.. if someone is unaware of Ctrl + Shift + Enter

=INDEX(B2:B15,INDEX(MATCH(1,(A2:A15="G23")*(D2:D15="R"),0),,))
 
Is working great!!! thank you all...
Another one...

When the formula don't find a value, shown "#N/A"

My question is:

How I can do to instead #N/A shown 0 or something else even space is fine

Why, because I have a sum in the bottom of column and you guys knows
 
Hi Daniel Valle..

Try to search for Iferror function..
an awesome :awesome: function.. for those who expect lots of error.. :)
 
Thank you Debraj for you help....but I never used this function... I'm not expert ( I wanna be )

Can't you tell me how?
 
Simply include your actual formula in IfError function.. like..

=IFERROR(YourActualFormula," ")

Where your actual formula may be "A1+B1".. (excluding = sign).. and then the required display character.. like 0 or space..
In above case it was.. Space..

as per your above screenshot.. this function is available in your version of excel..
 
Last edited:
Back
Top