Lookup help, multiple columns

texasduckhunter

New Member
Joined
Jul 21, 2019
Messages
7
I'm unsure what formula I need to return an adjacent value to the min value from a set of columns...

My excel knowledge is very limited

I have data in cells BC20 to CJ20 and down to BC136 and over to CJ136

I have numbers in every other column and text in between

example:
BC20 has pricing
BD20 has lead time
BE20 has pricing
BF20 has lead time
and so on

I have a formula in CK20 to pull min value, im sure this is probably not the easiest way but its all I know [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=MIN(BC20,BE20,BG20,BI20,BK20,BM20,BO20,BQ20,BS20,BU20,BW20,BY20,CA20,CC20,CE20,CG20,CI20)

In column CL i pull in the vendor information from the column titles
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX($BC$19:$CB$19,MATCH(CK20,BC20:CB20,))

Those seem to work great

But my issue is that i need the corresponding lead time to pull into CM from the Min value i have in CK

HELP PLEASE :)[/FONT]<strike>
</strike>
[/FONT]
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
Try this

In the CK20 cell you can put the array formula

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),BC20:CJ20))

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


--------------
in CM20:

=INDEX(BD20:CJ20,MATCH(CK20,BC20:CI20,0))
 

texasduckhunter

New Member
Joined
Jul 21, 2019
Messages
7
when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor

Try this en CK20

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),IF(BC20:CJ20<>"",BC20:CJ20)))

Is an array formula to accept must be press Shift + Control + Enter.
 

texasduckhunter

New Member
Joined
Jul 21, 2019
Messages
7
That seems to have done the trick!

Thank you very much!
 

texasduckhunter

New Member
Joined
Jul 21, 2019
Messages
7
DanteAmor,

you did so well the last time, i tried copying what you did with the following adjustments and it didnt work:

I have data in cells CP20 to DW20 and down to CP135 and over to DW135

I have numbers in every other column and text in between

example:
CP
20 has pricing
CQ
20 has lead time
CR
20 has pricing
CS
20 has lead time
and so on

i need the same kind of help please.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,095
Office Version
2007
Platform
Windows
DanteAmor,


I have data in cells CP20 to DW20 and down to CP135 and over to DW135

=MIN(IF(ISODD(COLUMN(CP20:DW20)),IF(CP20:DW20<>"",CP20:DW20)))

Is an array formula to accept must be press Shift + Control + Enter.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,819
Messages
5,483,087
Members
407,379
Latest member
Bender1964

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top