Lookup data

[Tosca]

Hello everyone

I have Excel 2003 and a grid of data. Columns A, C and E contain unique
data (one instance of each data value in each column and within the whole
grid) and the data in each of the columns is sorted alpha-numerically (A & B
relate to each other as do C & D and E & F). I need to be able to lookup a
particular value in the whole grid and return the contents of the cell
immediately to the right of the cell that matches the data. The match will
be exact. I had thought about using VLOOKUP but that can't be used to refer
to the whole grid of data (so far as I'm aware) - it would have to refer to
data contained in columns A & B or C & D or E & F. The data that is being
sought may be in any of these columns and this is out of my control.

I'm sure that this is simple - but it's beyond me! How can I perform this
lookup?

Thanks in anticipation.

 

[Max]

Assuming the source data is in cols A to F, from row1 down in Sheet1
(with unique data as stated)

In Sheet2
---------
With the look-up value in A1,

Put in B1:

=IF(ISNA(MATCH(A1,Sheet1!A:A,0)),IF(ISNA(MATCH(A1,Sheet1!C:C,0)),IF(ISNA(MAT
CH(A1,Sheet1!E:E,0)),"",INDEX(Sheet1!F:F,MATCH(A1,Sheet1!E:E,0))),INDEX(Shee
t1!D,MATCH(A1,Sheet1!C:C,0))),INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)))

 

[Guest]

tr
if(iserror(match(comp,A:A,0),ifiserror(match(comp(C:C,0),vlookup(comp,E:F,2,false),vlookup(comp,C;D,2,false),vlookup(comp,A:B,2)

 

[Tosca]

Wow - thanks for the help with these meaty formulae! I'll try them both and
see what happens.

A further "complication" is that, although I mentioned three pairs of
columns of data (A & B, C & D and E & F), the columns are likely to extend
so I could have 50 or 100 pairs of columns! Is there any way that this idea
could be extended easily? I see that each of these solutions refers
specifically to various columns between A and F. I know that I could extend
the formulae "manually" (to include column AF, for instance) but the more
complicated the formula, the more likely I am to make a mistake and have
problems debugging it.

I guess an ideal formula would include something like <A:AF> which would
refer to the whole grid of data. Logic says (to me!) that, once the
matching bit of information is found, couldn't OFFSET be used to determine
the data value one cell to the right?

Thanks again for any further input.

 

[Alan Beban]

Hello everyone

I have Excel 2003 and a grid of data. Columns A, C and E contain unique
data (one instance of each data value in each column and within the whole
grid) and the data in each of the columns is sorted alpha-numerically (A & B
relate to each other as do C & D and E & F). I need to be able to lookup a
particular value in the whole grid and return the contents of the cell
immediately to the right of the cell that matches the data. The match will
be exact. I had thought about using VLOOKUP but that can't be used to refer
to the whole grid of data (so far as I'm aware) - it would have to refer to
data contained in columns A & B or C & D or E & F. The data that is being
sought may be in any of these columns and this is out of my control.

I'm sure that this is simple - but it's beyond me! How can I perform this
lookup?

Thanks in anticipation.

Unless I'm missing something this question was asked in this forum on
April 29th in a thread entitled "vlookup from multiple columns",
although that post referred to Columns B, D, and F instead of A, C, and
E. I posted a response (as did Peo Sjoblom) that day. Here is a copy of
my response:

<<One way, if your data is in a range named "Tbl2" and your lookup value
appears in Cell I2, you could insert the following formula in a cell and
copy down as many rows as there are rows in Tbl2 (I refer to this as the
output range). The sought result(s) will be returned to the cell(s) in
the output range corresponding to the row(s) of the sought value in Tbl2.

=IF(ISNA(INDEX(INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2,INDEX(Tbl2,ROW(A1),0),0)+1)),"",INDEX(INDEX(Tbl2,ROW(A1),0),1,MATCH($I$2,INDEX(Tbl2,ROW(A1),0),0)+1))

This formula assumes that there are no duplicate numbers in B,D,F of any
single row of Tbl2. I haven't considered whether it can be readily
modified to deal with duplicates in a single row.

For any who might use the formulas from the freely downloadable file at
http:/home.pacbell.net/beban, the following formula can be entered in a cell

=OFFSET(INDIRECT(ArrayMatch(I2,Tbl2,"A")),0,1)

This formula assumes that there are no duplicate numbers in Columns
B,D,F. If there are it can be modified to

=OFFSET(INDIRECT(INDEX(ArrayMatch(I2,Tbl2,"A"),n,1)),0,1)

where n is the number of the occurrence of the lookup value, counting
across the first row of Tbl2 left to right, then down to the next row
and continuing left to right, etc. >>

I tested it only on a 6-column table, but I don't see any reason it
shouldn't work on the extended table you describe.

Alan Beban

 

[Domenic]

Do Columns B, D, and F contain numerical values? If so, try...

=SUMIF(A1:E100,G1,B1:F100)

OR

=SUMIF(A:E,G1,B:F)

....where G1 contains your lookup value. Adjust the ranges accordingly.

Hope this helps!

 

[Domenic]

Maybe...

=INDEX(A1:F10,MATCH(TRUE,MMULT(--(A1:F10=G1),TRANSPOSE(COLUMN(A1:F10))*0+
1)>0,0),SUM((A1:F10=G1)*(COLUMN(A1:F10)-COLUMN(A1)+1),0)+1)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Tosca]

Hi Alan and Domenic

I was unaware of the fact that this had been addressed recently! It's ages
since I visited this forum. I'll certainly have a look at your suggestions.

The data that is being sought is text in the format <GHI - P56> and the data
that should be returned is also text. There will be only one occurrence of
<GHI - P56> in the whole grid of data.

Thank you for your time.