Table Search For Codes

[bluemanuk1]

Hi all,

I am having trouble creating an IF AND formula to search for data. I was hoping someone could help. I have attached a file with 2 sheets.

The sheet Glass sizes is the reporting sheet, Overall Data sheet is the source data.

I am looking for a formula that searches the table in the Overall Data sheet for the data in cell A2, in this range A1:Z248 then fine the data in B2 and searches column AA then it pastes the data in AB of the same row. The same function is required in cell D2. This needs to be able to be copied for all blank cells.

The data in Overall Data doesn't change.

I have highlighted the test cells in yellow on each sheet. Hope this makes sense.

Thanks in advance.

[AliGW]

C2:

=INDEX('Overall Data'!$AB$2:$AB$248,MATCH(1,('Overall Data'!$R$2:$R$248=A2)*('Overall Data'!$AA$2:$AA$248=B2),0))

D2:

=INDEX('Overall Data'!$AC$2:$AC$248,MATCH(1,('Overall Data'!$R$2:$R$248=A2)*('Overall Data'!$AA$2:$AA$248=B2),0))

[JohnTopley]

Helper column to determine column of "Prefix"

=SUMPRODUCT(('Overall Data'!$A$2:$Z$228=$A2)*COLUMN($A$2:$Z$2))/COUNTIF('Overall Data'!$A$2:$Z$228,$A2)

in C2

=INDEX('Overall Data'!AB$1:AB$248,AGGREGATE(15,6,ROW($A$1:$A$248)/(INDEX('Overall Data'!$A$1:$AB$248,0,$E2)=$A2)/('Overall Data'!$AA$1:$AA$248=$B2),1))

copy across to D2

Similar for other selections (change highlighted value for appropriate helper column)

[bluemanuk1]

AliGW
Re: Table Search For Codes

C2:

=INDEX('Overall Data'!$AB$2:$AB$248,MATCH(1,('Overall Data'!$R$2:$R$248=A2)*('Overall Data'!$AA$2:$AA$248=B2),0))

D2:

=INDEX('Overall Data'!$AC$2:$AC$248,MATCH(1,('Overall Data'!$R$2:$R$248=A2)*('Overall Data'!$AA$2:$AA$248=B2),0))

If I copy the formula down the column I get #N/A. In the middle part of the code ('Overall Data'!$R$2:$R$248=A2) can it be changed to a bigger range to search the whole table rather than just the R column?

[bluemanuk1]

Hi John,

I think my post may have not explained that I have highlighted the BDL code in the Overall Data sheet as an example. There are other table that will need searching. I have reattached the file with a 3rd Sheet to show the full tables that need searching. Hope that makes sense.

[JohnTopley]

Re-read my post: you will need helper columns for EVERY table which I illustrated with the 2 tables in your test file.

Given your tables are in pairs a minor adjustment to my formula would allow you to drag them down columns L and O,

[JohnTopley]

And you need to get the "Prefixes" in your table aligned with your data table as you have inserted a blank space "33 (78/60)" vs "33(78/60)"

Columns G and R have corrected values: need to copy/paste values into your tables.

[JohnTopley]

I assumed each prefix was associated with a single column but I discovered that FPP-V U3 is in more than one column so the SUMPRODUCT approach to finding the column will not work.

Several others have the same problem: if this is not an error you may need VBA to provide a solution.

See attached.

[JohnTopley]

Please Login or Register  to view this content.

I discovered there are duplicate values for a given Prefix/Code: as an example, see BDL in attached

[bluemanuk1]

Hi John,

Thank you for all your work, this is amazing, I can fill in the few blanks. Really appreciate it.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.