Lookup Function : still missing the point

Here's an example using the "convert from 2D to 1D and then do lookup" approach:

The formula in B2 of 'Dinner set' Table, filled down:

=IFERROR(IF(MATCH(A2,2D Lookup::A,0),"✔︎",""),"")


The formula in A2 of the '2D Lookup' table, filled down the column until you see out of range error triangles:

=INDEX(UNION.RANGES(FALSE,Storage::$A$1,Storage::$A$2:$D$8),ROW())

The equivalents in French are:

=SI.ERREUR(SI(EQUIV(A2;2D Lookup::A;0);"✔︎";"");"")

=INDEX(INTERVALLES.UNION(FAUX;Storage::$A$1;Storage::$A$2:$D$8);LIGNE())

Note that to use this formula you need to have header rows in your tables. (I mention this because you don't show Header Rows in your examples.)

There are other automated ways to convert data from 2D to 1D, including just doing it manually. Doing it manually can be quick if your list is not that long. In any case. In any case, it's a common operation with data analysis using spreadsheets.

SG

lionnelfromparis wrote:

My problem :

-the value to look for in Table 1

-a 2 diemensionnal array in Table 2 where to look for

-return True/False if the value is found.

Hi Lionnel,

If you are looking for a TRUE/FALSE then you can do something like this, using just the MATCH function:

The formula in B2 of Table 1:

=IFERROR(IF(MATCH(A2,Table 2::A,0),TRUE,FALSE),FALSE)

This will return TRUE if the value in A2 of Table 1 is also in column A of Table 2. Otherwise, it returns FALSE.

Note that you can "widen" the matches by using the * wildcard, something like this:

The formula in B2 of Table 1-2:

=IFERROR(IF(MATCH("*"&A2&"*",'Table 2-1'::A,0),TRUE,FALSE),FALSE)

This returns TRUE because the E is found "within" the values in column A (both CDE and DEF) of Table 2-1. It will return FALSE if you put, say, G.

Substitute ; for , in the formulas if your regions uses , as a decimal separator.

SG

Hi Lionnel,

Numbers does not work well with arrays so searching an entire table for a match is not something that is easily done.

Again, I would like to ask for the big picture. What use is the info that something is present somewhere in table 2 going to be put to? You are trying to do a hard thing with a very meagre return. Just put the values in table 2 in a single column and work with that.

A checkbox is an input device and cannot display retrieved information. With conditional highlighting you could turn the cell black for TRUE and the font white for FALSE which might satisfy your desire.

quinn

Hi Lionnel,

I think you will find the Numbers is very good at this kind of "lookup" for matches in another column somewhere. It's a common task with spreadsheets, and worth learning. It's not that hard to accomplish.

Here is one way:

The formula in C2, filled down:

=IFERROR(IF(MATCH(B2,Table 2::C,0),"✔︎",""),"")


MATCH simply tells Numbers to look for the value in B2 in column C of Table 2. If it finds a match it reports the row number (which you don't need, because you just want TRUE or FALSE). The IF tells Numbers to put a checkmark if there is a match and nothing if there isn't.

MATCH returns an error when it doesn't find a match, hence the use of IFERROR which tells Numbers to insert a blank instead of an error.

In French it looks like this:

=SI.ERREUR(SI(EQUIV(B2;Table 2::C;0);"✔︎";"");"")

SG

Hi Lionnel,

As Wayne has stated in order to help with your specific problem we would need specific details.

As a more general point of information here is a little bit on using the combination of the formulas INDEX and MATCH to address your lookup needs.

We have our lookup table. Every letter has a number value associated.

The report table is where we have our lookup function.

B2=INDEX(lookup::B,MATCH(A2,lookup::A,0),column-index,area-index)

The first argument in INDEX is where to retrieve the result from. This is going to be from Column B in the table lookup.

The second argument in INDEX is what row (of column B) to receive data from (row-index). Here we are using MATCH.

The first argument in MATCH is what we are looking for. In this case we are looking for the value in A2.

The second argument in MATCH is where are we looking. In this case we are looking in Column A in the table lookup.

The third argument in MATCH is what is our matching-method. We could find the cell with the largest value that is close, find the cell with the smallest value that is close or find the value. In this case we are going to find the value.

We are not using column-index in this case.

In this case we have used INDEX/MATCH to do the work that could be done with LOOKUP or VLOOKUP. INDEX/MATCH is a little more flexible. If we wanted to do the work of HLOOKUP, we would have used MATCH as the column-index and left row-index blank.

Please feel free to ask questions of this example or to post your specific issues with a screenshot and some details.

quinn

lionnelfromparis wrote:

As Quinn was saying, I'm probably asking too much to Numbers

No, I don't think it is asking too much. Numbers and other spreadsheets can easily handle this kind of problem. A common approach is to convert the 2D to 1D and look for a match in the 1D. The conversion from 2D to 1D can be automated. (Of course it is much simpler if you just use 1D to begin with, if, for example, you have a simple list. Example to follow.

SG

In oder to specifically help, you will need to provide specific information about the data you have and what you want to look up. screenshots are very helpful.

Here is a link to help posting screenshots:

https://discussions.apple.com/docs/DOC-6591