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

gowseph

New Member
Joined
Apr 23, 2014
Messages
10
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>
123123
123
123
123John
456Jeff
789Martin
#N/A#N/A#N/A

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
</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?!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
The issue is probably due to your concate being a string and not a value. Try to wrap your concate in =value()
 
Upvote 0
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!
 
Upvote 0
I prefer index match,

Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top