Index/Match for an entire table rather than a single column

Question

I am looking for a formula that will locate an exact match of a value somewhere within a table of given values.

Table d,e,f contains the differences between values in columns a and b, but that's not too important. I'm interested in finding the values in column g within this table d,e,f.

Please see the photo for reference.Here

Column G contains four values that I want to locate in my table d,e,f. If the number in column G is found within the table, I want excel to output this number in column h. If this number is not within the table, I want either an error message or nothing at all to be inputted into its respective column h cell. (this is the case for 312,600 in the photo i've attached.)

I dont want to use a =vlookup() function because I will not necessarily know the column number in which said number is located. I want excel to scan the range of cells - d2:f13 - to look for this number in any of the columns.

I'm sure there is an index/match formula that will scan the range of cells d2:f13 to find the lookup value (those values in column G), but so far I can only get this function to scan one column at a time.

Any help would be much appreciated. Thanks

Answer 1

You can use simple Range=value expression wrapped in SUM & IF to manipulate the result. This is not INDEX MATCH combination that actually locates/looks up the value but a simple manipulation of Range=Value and return the same value if TRUE.

In this example sample data is in cells D1:F8. The lookup values are in cells H2 thru H8. Now in I2 put the following formula and then press Ctrl+Shift+Enter from within the formula bar to create an Array Formula. The formula shall now be enclosed in Curly Braces to indicate that it's an Array Formula.

=IF(SUM(IF($D$1:$F$8=H2,1,0))>=1,H2,"")

See the below screenshot. There could be a SUMPRODUCT alternative too if you don't wish to use Array Formula just in case.