Formula to search in a big table

Hasse

New Member
Joined
Jan 5, 2018
Messages
8
Hi,

If I have a table like this but with columns all the way to AP. How can I write a formula so that I can search for a for value in column B, D, F etc down to column AO to get a return value from the column to the right

/Hasse




ABCDEFGHIJKLMNOP
13000122855302012299830401?
23000222856302022299930402?
330003228573020323000
430004228583020423001
530005228593020523002
630006228603020623003
730007228613020723004
830008228623020823005
930009228633020923006
1030010228643021023007
1130011228653021123008
1230012228663021223009
1330013228673021323010
1430014228683021423011
1530015228693021523012
1630016228703021623013

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
Do you need the return value from the cell directly right from the column where you found it?
e.g, in you example: If I find 22865 in the second column, I return 30211
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873
Hi try this:

In each row you must enter the first 2 values (cells A1 and B1) then

C2 =OFFSET(A1,,)+IF(MOD(COLUMN(C1),2)=0,143,200)

And drag down and right

ABCDEF
1300012285530201229983040123141
2300022285630202229993040223142
3300032285730203230003040323143

<colgroup><col width="70" span="7" style="width:52pt"> </colgroup><tbody>
</tbody>
 

Hasse

New Member
Joined
Jan 5, 2018
Messages
8

ADVERTISEMENT

I want it to be the other way so when I type 30011 I want a return value of 22865
 

Hasse

New Member
Joined
Jan 5, 2018
Messages
8
It is but I try to extend so I can search in a table with a total of 42 columns
 

admiral100

Well-known Member
Joined
Jan 17, 2015
Messages
873

ADVERTISEMENT

Try this : just enter values in column A

B1 =IF(A1=30001+ROW(A1)-1,A1-7146,OFFSET(A1,,-1)+IF(MOD(COLUMN(B1),2)=0,143,200))

ABCDEFGH
13000122855302012299830401231413060123284
23000222856302022299930402231423060223285
33000322857302032300030403231433060323286
43000422858302042300130404231443060423287
53000522859302052300230405231453060523288
63000622860302062300330406231463060623289
73000722861302072300430407231473060723290
83000822862302082300530408231483060823291
93000922863302092300630409231493060923292
103001022864302102300730410231503061023293
113001122865302112300830411231513061123294

<tbody>
</tbody>
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,341
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Two things:

First, @Hasse, your sample table in this thread is misleading, you should have used the same sample you used in your other thread asking the same question as I posted the link to in Post #3 . And you probably should have just "bumped" your other thread rather than starting a new one (Please excuse me for mentioning it, MODs)

Second, the helpers/responses in this thread are misunderstanding the OPs request.
@Hasse, I've replied to your original thread, please see if my solution works for you.
 

JumpingCrab

Board Regular
Joined
Dec 27, 2017
Messages
96
Office Version
  1. 2019
Platform
  1. Windows
jtawk, I just checked out your response in the other thread.
That is a very creative use of the SUMIF function. I did not know that it worked in a way that it would just shift columns depending on the column in which the match is found. Is that even documented somewhere?
I will have to remember this one.

Thanks,
JL
 

Hasse

New Member
Joined
Jan 5, 2018
Messages
8
To you who has looked at this and trying to help I must say thanks. I was given attention that I was expressing the question wrong so it became misleading what I wanted. Anyhow I have been helped and now my problem is solved. But some of the solution you send me was although interesting so I gonna save them if I creating tables some other times

Once again, thanks for help:)
 

Similar threads

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

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
Top