Combining 3 cells into 1 cell, then referencing this cell in a lookup?

[gowseph]

I've tried using both: =A1&""&B1&""&C1 (also without the "") and =CONCATENATE(A1,B1,C1)

However when referencing them in a simple lookup formula it gives an incorrect output.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

123
			123
			123
John
Jeff
Martin

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]123[/TD]

[TD="align: right"]456[/TD]

[TD="align: right"]789[/TD]

[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]

</tbody>

The top left are the 3 methods I know of combining 3 cells into 1. The #N/A formula references each one individually: =lookup(D1,A4:B6)

The answer I am looking for here is John, yet in the actual spreadsheet I am getting the bottom-most value in the 2nd column every time, in the example I typed out above I am just getting #N/A Error Did not find value '123' in LOOKUP evaluation.

Any ideas please?!

 

[Roderick_E]

The issue is probably due to your concate being a string and not a value. Try to wrap your concate in =value()

 

[njimack]

Try either:
=(A1&B1&C1)+0
=--(A1&B1&C1)

 

[gowseph]

The issue is probably due to your concate being a string and not a value. Try to wrap your concate in =value()

Thank you, this got it working in the example I posted.


However, for some reason it is still not showing the correct result in the actual sheet.


The lookup table is 2 columns by 27 rows, every time I do it, it just returns the number in the lowest row. Regardless of my input e.g. 123 or =value() as above.


The cell it should be returning has a formula of =1-G22+1
Where G22 is 0.993674 formatted as a number.


Starting to drive me crazy!

 

[Roderick_E]

I prefer index match,

=index(range of results,match(d1, match range,0))

 

[gowseph]

I prefer index match,

=index(range of results,match(d1, match range,0))

Thank you so much, I too now prefer index match! A huge sigh of relief was heard across Bristol as I got the result I was looking for from the formula. Thanks again.