find values in multiple columns specific to a row value

[eastydie]

Hello everybody! it's been a long time,

I am not sure if there's already a post about this, if so, please paste the link or something here I have been googling and all that internet stuff about this and most I find are of different cases.

Anyhoo,

my problem is this:
I have two excel files.
File 1 consist of constant data (if you guys could refer to the excel, they are the ones in yellow headers)
File 2 consist of raw data where I am putting a formula (the blue headers)

Basically, the columns C-G can be used depending on the value of column A and B.

So for example:

A is blue
B is small
C is crayons

for it to be okay, File 1 should have a row that consist of:
A: blue
B: Small
which can be found in row 5
and since 'crayons' can be found in column G, then the entry is okay. And should give a value that says "OK".

I do hope you guys can help me on this.. the actual file that I have consists of not just column C-G but from G to AD. That's 24 columns!

Anything is highly appreciated. As much as possible, I would be very thankful if this is just in formula. But if it can't be, I'm also open to Macro

Thanks in advance guys.

[FDibbins]

not sure how you determine which column to pick, but based of you're example, try this...

I added a helper column at H, and used thois, copied down...
=A2&B2
then on the "other workbook, (I put this in M) use this...
=INDEX($A$1:$H$8,MATCH(J2&K2,$H$1:$H$7,0),7)

However, Im not sure how you arrive ay the values in L3:L8?

[eastydie]

Waw. Thanks FDibbins for your prompt reply!

L3:L8 are given. They are part of the raw data. I think your INDEX formula can be done using VLOOKUP too. using the concatenate function inside it. All those in blue headers are the raw data.. and the yellow headers are the basis for the raw data for it to be okay'ed.


I tried your formula and I think it doesn't give me the result that I need. Please let me know if which part of my question is a blur

[FDibbins]

No, you cant use vlookup to look "backwards", thats why I used index/match

ok, so, in you're example, you would be looking for values in the stuff5 column?

if so, if you're headings in both instances are identical (stiff5 and stuff5), then the index/match can be made more flexible like this...

=INDEX($A$1:$H$8,MATCH(J2&K2,$H$1:$H$7,0),MATCH(M$1,$A$1:$G$1,0))

and to error-trap, wrap it in iferror...
=IFERROR(INDEX($A$1:$H$8,MATCH(J2&K2,$H$1:$H$7,0),MATCH(M$1,$A$1:$G$1,0)),"")

[eastydie]

Oh yeah! It is working backward..Right right.. sorry. My bad.

I would be looking all the values in Stuff1 to Stuff5 (Column C to G).. But it should be dependent to columns COLOR and SIZE..

So for example,
Please look into the blue headers:
the row 8: ORANGE LARGE PAD

this entry is not OK since, base on the yellow headers, the only ORANGE LARGE there is CRAYONS.. and not PAD. So, the output should be "Not OK."

Please let me know if you need more. You have no idea how grateful I am for your replies

[FDibbins]

ok, not sure I understanbd what you are using to determine which column to search in, but the monster below searches for the value in L, in each column C:G and compares what it finds with the info in L, and returns either TRUE or FALSE

=IF(INDEX($A$1:$H$8,MATCH(J4&K4,$H$1:$H$7,0),3)=L4=FALSE,
IF(INDEX($A$1:$H$8,MATCH(J4&K4,$H$1:$H$7,0),4)=L4=FALSE,
IF(INDEX($A$1:$H$8,MATCH(J4&K4,$H$1:$H$7,0),5)=L4=FALSE,
IF(INDEX($A$1:$H$8,MATCH(J4&K4,$H$1:$H$7,0),6)=L4=FALSE,
IF(INDEX($A$1:$H$8,MATCH(J4&K4,$H$1:$H$7,0),7)=L4=FALSE,FALSE,TRUE),TRUE),TRUE),TRUE),TRUE)

[eastydie]

Hahahaha! Woooooooooooooh! Amazing! It works! Your monster rocks!!

Thank you soooo much!

[FDibbins]

glad I was able to help, and thanks for the rep